Excel VBA: UserForm Image from Worksheet (3 Cases)

While creating or modifying a UserForm, you may need to add or create images from the worksheet. In this instructive session, I’ll demonstrate 3 methods to add an image from a worksheet to UserForm in Excel VBA with proper explanation.

What Is VBA UserForm?

UserForm is mainly a dialog box that provides an interactive interface for users’ convenience. Most likely you are accustomed to using the VBA code in a module. But if you want to add more things and develop a user-friendly interface, UserForm may fulfill your requirement. There are a myriad of reasons for using the UserForm.

Excel VBA: UserForm Image from Worksheet (3 Cases)

Here, you’ll see 3 methods. The first one is about the simple copy and paste tool. Later, the way of generating images directly from the worksheet range will be discussed. Lastly, you’ll see how to add an image utilizing the VBA code into the UserForm.

Let’s explore the methods.

1. Using the Simple Copy Pasting Tool

In the beginning method, you’ll see the simple copy-paste tool to copy an image from the worksheet to UserForm in Excel VBA within seconds.

Let’s say, you have an image on your worksheet. Now, you need to add this image to the UserForm dialog box.

Right before that, you must know how to create a VBA UserForm. Briefly, I am showing the process here.

➥ Firstly, go to the Developer tab and then click on the Visual Basic option (the keyboard shortcut for opening the Visual Basic Application is ALT + F11).

➥ Secondly, click on the Insert tab and choose the UserForm option.

Then, you’ll see the following dialog box namely UserForm1.

Note: Generally, you’ll see a floating window i.e. Toolbox while creating a UserForm. But if you don’t see such a window just go to the View tab and choose the Toolbox option.

Let’s come to the topic!

Choose the Image option from the Toolbox box and draw a frame for the image by dragging simply. Thus, you can create Image Control. Look at the following screenshot.

Now, if you right-click while keeping the cursor over the image, you’ll see some options and pick the Properties option. Immediately, you’ll see the properties at the lower-left part of the window.

Right now, go to the worksheet again and copy the image by pressing CTRL + C.

After copying the image, go to the UserForm1 >  Properties of Image1 and select the (None) option of the Picture field as depicted in the following image.

Therefore, paste the image by pressing CTRL + V simply.

Eventually, you’ll get the image of the worksheet in your UserForm.

2. Create Worksheet Range Directly as Image to UserForm

In this method, you’re going to explore an outstanding way that directly creates an image from your specified range in a worksheet and then copy the image into the UserForm simultaneously.

Assuming that Sales of some Products are provided for two years i.e. 2020 and 2021. The entire dataset including the heading is available in the B3:D12 cell range. However, you may add this entire dataset as an image to the UserForm directly!

Step 01: Creating a Button

Initially, insert a Button (from the Form Control) from the Insert option in the Developer tab. Right before that, create a new UserForm as shown in the first method and it will be the UserForm2.

Then, rename Button1 as the Open UserForm.

Next, right click on the button and choose the Assign Macro option.

Now, copy the following code into the module to show the UserForm2.

Sub Button1_Click()
UserForm2.Show
End Sub

Step 02: Adding a Command Button

At this stage, you have to insert the CommandButton option from the Toolbox. Then, rename the button as Generate Picture.

Step 03: Assigning the VBA Code

If you double-click on the Generate Picture button, you’ll get the space of inserting code. Then copy the following code.

Private Sub CommandButton1_Click()
Range("B3:D12").Copy 'Choose your Worksheet Range
Range("H4").Select 'Specify the Location of Keeping Picture Temporarily
ActiveSheet.Pictures.Paste Link:=True
ActiveSheet.Pictures.Select
Application.CutCopyMode = False
Dim tChart As String, tPicture As String
Dim imgWidth As Long, imgHeight As Long
Application.ScreenUpdating = False
tPicture = Selection.Name
With Selection
imgHeight = .ShapeRange.Height
imgWidth = .ShapeRange.Width
End With
Charts.Add
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet2" 'Type your Sheet Name
Selection.Border.LineStyle = 0
tChart = Selection.Name & " " & Split(ActiveChart.Name, " ")(2)
With ActiveSheet
With .Shapes(tChart)
.Width = imgWidth
.Height = imgHeight
End With
.Shapes(tPicture).Copy
With ActiveChart
.ChartArea.Select
.Paste
End With
.ChartObjects(1).Chart.Export Filename:="aC:\Exceldemy\MyPic.jpg", FilterName:="jpg"
.Shapes(tChart).Cut
End With
Application.ScreenUpdating = True
Set Picture = LoadPicture("C:\Exceldemy\MyPic.jpg")
ActiveSheet.Pictures.Delete
End Sub

In the above code, you have to keep in mind 4 things (yellow colored in the above image).

• Firstly, choose your worksheet range (e.g. B3:D12 cell range) and insert the cell range accordingly.
• Secondly, specify the location of the picture (e.g. H4 cell) where the picture will be created from the range. Later, the picture will be deleted automatically.
• Thirdly, type your sheet (e.g. Sheet2) from where you want to add an image.
• Lastly, define the location of your PC (e.g. C:\Exceldemy\MyPic.jpg) where the image will be stored and loaded automatically when required.

However, after copying the code, go to the worksheet and click on the Open UserForm.

Shortly, you’ll see the following dialog box of the UserForm2. Then, click on the Generate Image button.

Finally, you’ll get the following image into the UserForm2.

3. Add Worksheet Image to UserForm

Last but not the least, you may add an image from your worksheet using the VBA code into the UserForm.

For example, you want to create a chart from the following given data to display the variation of the sales clearly.

After creating the chart from the selected range of cells, you’ll get the following chart.

Now, save the chart as an image by right-clicking, and then, choose the option Save as Picture. I saved the picture as Chart1 in JPG format.

However, add a command button from the Toolbox and rename the button as Add Image.

Then, double-click on the button and assign the following VBA code.

Private Sub CommandButton1_Click()
 Set Pic = UserForm3.Controls.Add("Forms.Image.1")
    
    With Pic
        'Load Chart as a Picture to Image Control
        .Picture = LoadPicture("C:\Exceldemy\Chart1.jpg")
        
        'Align the Picture Size
        .PictureSizeMode = fmPictureSizeModeZoom
        
        .Left = 70
        .Top = 20
        
    End With
End Sub

In the above code, I set the Add method with the UserForm object for the Pic variable. Then, I utilized the LoadPicture method to add the image and the PictureSizeMode property to resize the image. More importantly, you have to change the location of the File Name parameter of the LoadPicture method.

However, if you run the code, (just press the F5 key), you’ll get the button in the UserForm3 dialog box. Right then, you’ll get the image when you click on the Add Image button.

Things to Remember

• Image Control supports only 6 specific image formats i.e. .bmp, .cur, .jpg, .ico, .wmf and .gif. So, make sure that your image is in those formats.
• Besides, you are not able to use the LoadPicture method without ActiveX Controls.

Download Practice Workbook

Conclusion

That’s the end of today’s session. I strongly believe this article helped you to copy and create images from the worksheet to UserForm effectively. Anyway, if you have any queries or recommendations, please share them in the comments section below.

Related Articles

• Excel VBA: Show Userform in Full Screen
• How to Create Toggle Button on Excel VBA UserForm
• How to Use Excel UserForm as Date Picker
• Excel VBA: Print UserForm to Fit on a Page
• How to Use VBA to Get Value from Userform Textbox in Excel
• Excel VBA to Format Textbox Number with UserForm
• How to Create a UserForm: an Overview