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

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!

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:

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!

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

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:

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.

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):

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?

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

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:

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:

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:

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!