Excel VBA Case Study #6 – Displaying Images in Excel using VBA User Forms

Last updated on April 30, 2019 By Victor Chan

Summary: It’s common to think of Excel just as a tool to track and report data. But what if we could show images in our spreadsheets – a picture is worth a thousand words (or data points) right?

We saw how User Forms (discussed in Case Study #5) can make worksheets usable for even the newest Excel users. And if we display images in those forms we can take usability to the next level.

For some folks (such as the park rangers in this study) their spreadsheets are pretty useless without images. However with images their workbook can be used by both the rangers and the park’s visitors!

In this case study, we’ll walk through how to select the right image from a spreadsheet and display it on a UserForm. The simple code that I share will give you a tool that allows you to build Excel VBA solutions that you never thought possible!

Difficulty: Intermediate

Download the Sample Workbook

Download the sample file with VBA code
Fish-Image-Lookup.xlsm (649 Kb)

#1 – Why did I create the Macro?

In the middle of Tall Oaks Park sits its main attraction – a five-acre pond teaming with a variety of freshwater fish. The park rangers stock some of the varieties, and others are native inhabitants.

Thousands of visitors a year sit along the pond’s open shorelines, many hoping to catch a whopper that they can take home for dinner. Unfortunately, most of the anglers don’t know the fishing regulations (i.e., the minimum size to be able to keep each fish species, and the maximum quantity).

Since the regulations change from month to month – depending on when the fish are stocked and how many have been caught – the park doesn’t have enough funding to print pamphlets containing them. Some of the regulations even change daily!

The rangers did, however, maintain the following Excel spreadsheet with the regulations:

Inside the ranger station was a kiosk…essentially a computer on which visitors could view weather conditions and access software on which they could look up bird varieties.

The rangers said that they could easily display the fishing regulation spreadsheet on that computer, but they couldn’t expect visitors to know how to use Excel!

After a couple conversations, I convinced the rangers that a user-friendly front-end to the Excel workbook would provide the visitors the information they needed – updated regulations without having to know anything about Excel!

#2 – What does it do?

When the rangers get into the station each morning, they turn on the kiosk computer, pull up Excel, and open the workbook containing the macro. After they update the regulations, they press this button on the regulations sheet to open the UserForm:

Pressing the button opens the following form:

On this form, users select the type of fish from the dropdown:

Once they select the fish, the form displays an image of the fish plus its minimum size and maximum quantity:

If the visitor wants to view the regulations for a different fish species, they simply select a different fish from the dropdown.

See, it is possible to display an image in a UserForm!

#3 – How is it better than using Excel without VBA?

Although the rangers’ spreadsheet contained all the information they wanted to relay to anglers, at least half of the anglers had never before used a spreadsheet. Since the rangers were already overworked, they didn’t have time to show the anglers how to use and view the spreadsheet.

Nearly all park visitors, however, knew how to select an option from a dropdown. Most did this on the web at least once a week, if not more frequently.

Adding the simple UserForm, particularly with its ability to display images of fish, allowed visitors to view updated fishing regulations daily…without any assistance from the rangers!

#4 –How does the code work?

Now I’ll show you how, with just a few lines of code, I was able to display the fish images in a UserForm.

How to add the images (DON’T just paste directly into Excel)

It’s worth mentioning that images must be added to a worksheet in a particular way in order for them to work with VBA.

Don’t just paste each fish image directly into Excel.

Instead first add a corresponding “Image” control using the following menu item:

After moving the Image control to the right place, I opened the control’s properties by right-clicking the image and clicking on “Properties”:

I then added each fish image by copying the image, double-clicking inside the “Picture” property, and clicking CTRL + V.

I named each image after the fish it represented by updating the “Name” property.

Editor’s Note: Later on if you want to edit the image properties you’ll need to enter “design mode” in the Developer tab. Otherwise you won’t be able to select the inserted images.

User Forms

Before writing the VBA code, I built the UserForm using the same basic steps in Case Study #5.

From the VBA editor, I inserted each UserForm using the “Insert” menu:

After I inserted the form, I modified its color and captions and inserted a frame using the steps I took in the last case study. I also inserted text boxes and labels to allow me to display the minimum size and maximum quantity.

I then added the dropdown (called a ComboBox in VBA terminology) by clicking on the following from the toolbox:

The dropdown allows anglers to select a species of fish. Finally, in the center of the frame, I added an “Image” control via the Toolbox:

It’s this image control that will eventually display each fish image.

VBA to Populate ComboBox

When the UserForm first opens, I want to refresh the list of fish species available in the dropdown. I do this so that, in case a ranger has updated the species available in the spreadsheet, the form will display the latest list.

I run this snippet of code when the UserForm first opens (initializes):

Option Explicit

Private Sub UserForm_Initialize()

Dim X As Integer

'Loop through the worksheet and put all of the fish values in the ComboBox
For X = 2 To 200

If Worksheets("Fishing Regulations").Cells(X, 2).Value = "" Then Exit For
ComboBox1.AddItem (Worksheets("Fishing Regulations").Cells(X, 2).Value)
Next X

End Sub

Since the rangers assured me that there would always be less than 199 fish in their spreadsheet, I wrote the above code to loop through rows 2 through 200 of the sheet. If the code finds a blank row (indicated by double quotation marks), it stops running.

Essentially, this code adds each type of fish (i.e., each value in column two of the spreadsheet) to the ComboBox using the “ComboBox1.AddItem” command. Pretty simple, right?

VBA to Display the image, Minimum Size, and Maximum Quantity

When the ComboBox value changes (i.e., when someone selects a type of fish), the following code runs:

Private Sub ComboBox1_Change()

Dim myfish As String
Dim X As Integer

'Change the Caption on the Form to display fish name
Frame1.Caption = ComboBox1.Value

'Set value based on selected fish in ComboBox
myfish = ComboBox1.Value

This snippet sets the value of the frame’s caption to the type of fish. It then sets the variable “myfish” equal to the type. The next two snippets use that variable:

'Loop through all the entries on the sheet to find selected fish they selected, based on name
For X = 2 To 200
If Worksheets("Fishing Regulations").Cells(X, 2).Value = _
    myfish Then 'Found match
UserForm1.TextBox1.Text = _
    Worksheets("Fishing Regulations").Cells(X, 3).Value 'Set size
UserForm1.TextBox2.Text = _
    Worksheets("Fishing Regulations").Cells(X, 4).Value 'Set quantity

This snippet loops through all the rows in the worksheet until it finds the fish species (stored in the “myfish” variable) selected from the dropdown. It then sets the values of the minimum size and maximum quantity text boxes equal to the corresponding values in the worksheet.

The final code snippet displays the appropriate image on the form:

'Case statement to find which image to use. Select case based on fishname, _
    and then set the image based on the correct fish image

Select Case myfish
Case "Bluegill"
  UserForm1.Image1.Picture = _
    Worksheets("Fishing Regulations").OLEObjects("Bluegill").Object.Picture
Case "Northern Pike"
   UserForm1.Image1.Picture = _
    Worksheets("Fishing Regulations").OLEObjects("NorthernPike").Object.Picture
Case "Rock Bass"
   UserForm1.Image1.Picture = _
    Worksheets("Fishing Regulations").OLEObjects("RockBass").Object.Picture
Case "Walleye"
   UserForm1.Image1.Picture = _
    Worksheets("Fishing Regulations").OLEObjects("Walleye").Object.Picture
Case "Yellow Perch"
   UserForm1.Image1.Picture = _
    Worksheets("Fishing Regulations").OLEObjects("YellowPerch").Object.Picture
End Select
End If
Next X

End Sub

This section of code uses a “Case” statement. This type of statement is used to find situations where a specific criterion is met. In the statement above, the fish species (i.e., the value of the “myfish” variable) is the criterion.

For example, if the value of “myfish” is “Bluegill”, the code will display the corresponding image from the spreadsheet in the Image control (Userform1.Image1.Picture). The “.OLEObjects(“Bluegill”).Object.Picture” code references the picture to be displayed.

Not too bad, right?

You can download a copy of the full workbook with all the VBA code in it from this link:

Download the Sample Workbook

Download the sample file with VBA code
Fish-Image-Lookup.xlsm (649 Kb)

#5 – Summary of key learning points

At a glance – and even after using it for years – Excel seems like a tool to track and report on data. Including images in a spreadsheet, let alone in a UserForm, isn’t even a consideration for most Excel users.

As you can see, though, as long as you have the images in a spreadsheet, displaying them on a form is much easier than it would appear!

As a VBA developer – and in life – it’s crucial to think outside-the-box. The first time a customer requested that I add an image to a UserForm, I had no idea how to do so. I didn’t know if it was possible, either…

But after a few hours of research and trial-and-error, I was able to add images to a UserForm and impress an important client!

Don’t ever assume that what seems impossible isn’t possible. With a bit of ingenuity and research, you can produce innovative and impressive Excel VBA solutions, too!

#6 – About the Author

Chris Cox – Two decades ago, Chris taught himself VBA to help him eliminate annoying manual Excel tasks. Twenty years later, Chris has turned that experience into a second career as a highly-rated independent consultant specializing in Excel and Excel automation. He loves exceeding client expectations by making their processes as efficient as possible!

Chris is also passionate about writing, editing, and proofreading, and he’s successfully done all three both in full-time employment and as a consultant.

Chris currently works as a full-time as a Senior IT analyst for a global asset management firm. He has also worked in government, healthcare, and consulting. He writes fiction in his spare time.

If you’re interested in working with Chris, please reach out to him directly at ctcox20@gmail.com or find him on his Upwork page.

Related Posts:

Connect on YouTube, LinkedIn, Twitter.

Hey, I'm Victor Chan

Are you struggling with complex Excel tasks? Feeling overwhelmed by spreadsheets that are hard to use?

Many people believe mastering Excel is about learning shortcuts, functions, and formulas. But this overlooks the importance of building practical, real-world applications. It's not just about knowing the tools. It's about using them effectively.

That's where I come in. You'll get a unique perspective to Excel training from me. I have over 20 years of experience at Deloitte and two global tech companies. And I know what can make a difference in your career.

Let me help you integrate Excel into your professional life. Starting today. Read one of my articles, watch one of my videos. Then apply the new technique to your work. You'll see the difference immediately!

Recommended Posts

Discover the PROVEN Blueprint for transforming your Excel skills, supercharging your productivity, and standing out in your career! My course helps you to learn Excel VBA and save hours of time even if you have zero prior experience with programming.

Solve tricky Excel problems and take your work to the next level! Get customized solutions for your unique needs. Save time and gain insights with truly expert Excel solutions from only $97 per task.

Get a clear overview of your project progress using the Excel project timeline. Use it to communicate the big picture, track task progress, and stay on top of your project goals. Stay organized with our project timeline!

Our cheat sheets provide quick and easy reference to commonly used Excel VBA concepts and code snippets.

Unlock new levels of productivity and efficiency with our cheat sheets, and write VBA code like a pro in no time.


Are you looking to upskill and stay ahead of the curve? Excel is a powerful tool that keeps growing in demand. We round up the best online courses for learning Excel.

Are you looking to up your spreadsheet game? Excel is an invaluable tool that can help you stay organized and save time. From data analysis to budgets, Excel can do it all!

Today, having Excel skills is more critical than ever. Those who know how to use Excel are more likely to find higher-paying jobs. And get promoted faster.


Step up your Excel game! Join our free email newsletter and get updates on how to become more awesome at Excel.