# How to Set Floating Picture in Excel (with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

In this article, I will show how to set a floating picture in Excel. In many situations, we need to keep a picture side by side when scrolling through the Excel worksheet. For example, there are many situations when we need to compare a long vertical list of data with a picture. Here I will discuss how to deal with this kind of situation. So let’s get started.

## Step-by-Step Procedure to Set Floating Picture in Excel

Before going to set up the Excel floating picture, let’s take a sample worksheet to contextualize the problem first. In the picture below, I have taken a worksheet where there is some information about 50 students of a College. The information includes Name, Student ID, Gender, and Blood Group. On the right side of the data table, we have a picture of the Blood Type Compatibility Table.

As we scroll down below, we want that the picture of the Blood Type Compatibility table floats with us so that we can easily check whether a student is able to donate or receives a certain group of blood. For example, even when we reach the bottom of the table, we want the picture to appear there at the top right corner of the screen. So how do we do that? To know, follow the steps below.

### Step-1: Opening Private Module in VBA

First, we need to open a private module in Visual Basic Window. To do that, follow the steps below.

• We first need to open the Microsoft Visual Basic Window. To do that, bring your mouse cursor to the bottom of the screen on the top of the name of the worksheet and then right-click on it. After that, select the View Code.

• As a result, the Microsoft Visual Basic Window will open up.

• Alternatively, you can also type Alt+F11 to open the editor and then double-click on the sheet.

### Step-2: Writing VBA Code in Private Module

In the 2nd step, we will write a code in the Module. Follow the steps below.

• Now, copy-paste the code below in the window.
``````'Right Right Corner
Private Sub Worksheet_SelectionChange(ByVal A_Target As Excel.Range)
Dim My_Picture As Object
Dim My_Top As Double
Dim My_Right As Double
Dim Top_Right_Cell As Range
'-----------------------------------------------------------
'- position of the bottom right cell
With ActiveWindow.VisibleRange
AA_r = 1
AA_c = .Columns.Count
Set Top_Right_Cell = .Cells(AA_r, AA_c)
End With
'------------------------------------------------------------
'- Setting position of the picture
Set My_Picture = ActiveSheet.Pictures(1)
My_Top = Top_Right_Cell.Top + 5
My_Right = Top_Right_Cell.Left - My_Picture.Width - 5
With My_Picture
.Top = My_Top
.Left = My_Right
End With
End Sub``````

How Does the Code Work?

Here, we have first created a private subroutine named Worksheet_SelectionChange. Then we created 4 variables named My_Picture, My_Top, My_Right, Top_Right_Cell, and My_Picture.  Then, we determine the position of the bottom cell with AA_r and AA_c. Next we set the top position of the visible window with My_Top = Top_Right_Cell.Top + 5 and the right position with My_Right = Top_Right_Cell.Left – My_Picture.Width – 5.

Read More: How to Create Floating Cells in Excel (2 Easy Methods)

### Step-3: Scrolling Down in Worksheet

In the final step, we will check whether our code is working properly or not by scrolling through the worksheet.

• Now, if we scroll down below, the moment we click on any cell, the floating picture will appear on the top right corner of the window.

• If you want your floating picture to be on the bottom right corner of the window, you have to copy and paste the following code on the developer window.
``````   'Bottom Right Corner
Private Sub Worksheet_SelectionChange(ByVal A_Target As Excel.Range)
Dim My_Picture As Object
Dim My_Top As Double
Dim My_Left As Double
Dim Bottom_Right_Cell As Range
'-----------------------------------------------------------
'- position of the bottom right cell
With ActiveWindow.VisibleRange
AA_r = .Rows.Count
AA_c = .Columns.Count
Set Bottom_Right_Cell = .Cells(AA_r, AA_c)
End With
'------------------------------------------------------------
'- Setting position of the picture
Set My_Picture = ActiveSheet.Pictures(1)
My_Top = Bottom_Right_Cell.Top - My_Picture.Height - 5
My_Left = Bottom_Right_Cell.Left - My_Picture.Width - 5
With My_Picture
.Top = My_Top
.Left = My_Left
End With
End Sub``````

• Now, if you scroll down and click any cell, the floating picture will appear on the bottom right corner of the window.

This is how easily we can have a floating picture in an Excel worksheet that can move with the visible window.

## Floating Box Visible While Scrolling in Excel

In this section, I will show you how to make a floating box visible while scrolling in Excel. Taking the previous example given above, this time we will try to make a floating box appear instead of a floating image. Here, we will show a box that will contain the number of students who has O +ve as blood group.

To do that, follow the steps below.

Steps:

• First, let’s have a cell(G5) where we have calculated the number of students who have O+ as blood group using the following formula.
`=COUNTIF(E5:E54,"O+")`

• Now, we will create a box that will contain the data of cell G5 and make the box float as we scroll. To do that, open the VBA editor by clicking Alt+F11. Then go to Insert> Userform.

• As a result, a Userform will be created like this.

• Now, insert a module by going to Insert > Module.

• Now, go back to the created UserForm1 by double-clicking on the menu bar located on the left side.

• Here in the bottom corner, you should see the properties of UserForm 1. If you don’t see the properties tab, you can right-click on the user form window and then select properties. Now, edit the caption and give it a suitable one. I am giving it Total Number of O+.

• Now, as the userform is empty, we can insert a text box or a label. I amtaking a label named Label1. To do that, click on the A icon from the Controls in the Toolbox and draw a box on the user form window.

• Now, right-click on the user from the window and select view code. Consequently, the Code window will open up.

• Now, choose Initialize from the dropdown menu. (see the screenshot below)

• Now,write down the following code and clear the above portion.
``````Private Sub UserForm_Initialize()
Me.Label1.Caption = Format(Worksheets("Floating_Box").Range("G5").Value, "#")
End Sub``````

Here,

In Label1.Caption, Label1 is the name of the label that we have previously added in the form hence, you need to write the name you put.

Worksheets(“Floating_Box”), Floating_Box is the worksheet name on which you want to apply this.

In Range(“G5”), G5 is the cell that you want to display.

• Now, click on the worksheet to open the code of that sheet. Then from the left and right dropdown menu, choose Worksheet and Calculate.

• Now, copy-paste the following code.
``````Private Sub Worksheet_Calculate()
UserForm1.Label1.Caption = Format(Worksheets("Floating_Box").Range("G5").Value, "#")
End Sub``````

• Now, we will write a code in Module1 to open a Macro. To do that, go to Module by clicking on the Module1 and write the following code.
``````Sub ShowForm()
UserForm1.Show False
End Sub``````

• Now, go to the worksheet and then press Alt+F8 to open the macro list. Then select the ShowForm macro and run it.

• As a result, you will see that the form will appear on the worksheet containing the total number of O+. And even if we scroll through the worksheet, the form will still float.

• Now, if we change O+ to something else, it will immediately reflect on the floating form.

Read More: How to Insert Floating Table in Excel (2 Easy Ways)

## Things to Remember

• The floating picture will only appear after you click on a cell while scrolling.
• After writing the given code, you can not use the Undo command(Ctrl+Z) . This is a major con of this code.

## Related Articles

Aniruddah Alam

Hi, I am Md. Aniruddah Alam from Dhaka, Bangladesh. I completed my Bachelor's degree in Naval Architecture from BUET. Currently, I am working as an Excel and VBA Content Developer. Here, I always try to present solutions to Excel-related problems in a very concise and easy-to-understand manner. In my leisure time, I love to read books, listen to podcasts, and explore new things.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF