In this tutorial I’ll show you how to have images appear and disappear by using a checkbox from the Format Controls of the Developer tab in Excel.
You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.
I’ve done several posts previously about how to select or pull in photos based on choices made from a data validation drop down list. Check these out – you might find them interesting and very useful. Here are the various links to those posts:
This tutorial is a bit different. Here we have checkboxes next to the names of the photos, and when a checkbox is selected, the photo will appear, when unchecked the cell will be blank:
The best way to truly understand the process is to watch the video. I will provide a cursory explanation of the process below.
First we have a worksheet called “Photos” that has the various photos that we want to use, including one just called blank:
Using the “Create from selection” on the Formulas tab, we will name the cells containing the photos the name that is in column A:
Next, after copying the names for the various photos to column “B” of the other worksheet, we’ll insert checkboxes from the Developer tab into column A and reference the cells in column C as the linked cell (notice I right-clicked on the checkbox then clicked on “Edit text” and held down my delete key to remove the text normally associated with the checkbox):
When we check the box, “TRUE” will appear in that linked cell, and when unchecked, “FALSE” will appear. Next, we’ll insert the following formula in the cells in column D:
So here is what happens when a box is checked (remember, “blank” is what we named the last cell on the “Photos” worksheet):
Now, we’ll copy the related photo, paste it in the cell in column E, and while it’s selected, create a new name (you can use the keyboard shortcut Ctrl + F3, then click on “New”), and enter an INDIRECT formula to reference the name in the cell in column D:
INDIRECT “returns the reference specified by a text string”. So this formula will take the text that’s in the cell in column “D” and convert it to its reference, which is the cell with that name on the “Photos” worksheet.
Then while the photo is still selected, enter an equal sign and the name of the formula you just entered into the formula bar:
And now you can see that when the box is checked, the photo appears, and when unchecked, a blank is inserted. Do the same to the rest of the rows, hide columns C & D, and you are all set!
I truly encourage you to watch the video. There are a few important details that will assist you in properly structuring your workbook for this to function best.
What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!