VBA Case Study – Displaying Images in Excel using VBA User Forms
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
![[Image] Download Workbook](https://www.launchexcel.com/media/blog/sample-workbook.png "Download Workbook"){kind=small}
Download the sample file with VBA codeFish-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 codeFish-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!
Hi, I'm Mr KING Admin of KINGEXCEL.INFO .Thank you so much and I hope you will have an interesting time with KINGEXCEL.INFO. Mail: quantriexcel@gmail.com - PHONE : +84779313987.