Showing a picture from a list of pictures

Content

Introduction

Many people keep part lists in Excel. Sometimes even with embedded pictures next to the part data.

This article demonstrates a method to show a picture on a sheet that changes when a different part number is chosen from the list of available part numbers.

Preparing the list

The first thing to do is to position the pictures properly. Each picture should go in a cell on the same row as a unique description (part number) of the part it pertains to.

Format the cells, so the picture fits in a single cell (either adjust the size of the picture, or adjust row height and column width):
screenshot showing a list of icons next to their description
Figure 1, screenshot showing a list of icons next to their description

Preparing the sheet that shows the dynamic picture

We need some way of knowing which picture is to be shown. We start off by defining a dynamic range name that will expand automatically when we add new items to our list (assuming they are on Sheet1, starting on cell A1, with one row of headings) Choose Insert, name define (In Excel 2007 and up on the "Formulas" tab of the ribbon, in the "Defined Names" group, click the "Define Name" button) and define this name:

Name
Refers to
PictureList
=OFFSET(Sheet1!$A$1,1,0,COUNTA(Sheet1!$A:$A)-1,1)

Let us say cell A2 on sheet2 will be used to enter the picture code we want to see the picture from. The formula in the next defined name will point to the cell to the right of the cell who's picture name matches the text entered in cell Sheet2!A2:

Picture
=OFFSET(Sheet1!$B$2,MATCH(Sheet2!$A$2,PictureList,0)-1,0,1,1)

Creating the picture

Excel 2007

There are two methods to insert the proper picture in Excel 2007. One is by adding the camera tool to your QAT and using that, the other is shown below.

Select the cell where you want your dynamic picture to appear and click the Insert tab of the ribbon.  On the far right, find the "Object" button and click it (see screenshot below).

Insert Object button on the ribbon
Figure 2, Insert Object button on the ribbon

The Insert object dialog appears:

Object Dialog
Figure 3, Object Dialog

Select the top option (Bitmap Image) and click OK.

Excel 2010

In Excel 2010, the Object button does not work, you have to either use the camera tool, or insert a picture from file (ane pciture file will do), after which you can change the formula.

After inserting the picture or object, hit escape to return to Excel. The newly inserted picture object should still be selected. The formula bar should show something like =EMBED("Paint.Picture";""). Click in the formula bar and replace the entire formula with this formula: =Picture

the picture's formula bar
Figure 4, screenshot showing the picture's formula bar.

Hit enter or click the green checkmark to the left of the formula bar.

Excel 97 to 2003

Select the cell with the topmost picture. Hold down the shift key and click the Edit menu. It should show "Copy Picture...":

part of Excel's Edit menu when clicked whilst holding down the shift key

Figure 4, screenshot showing part of Excel's Edit menu when clicked whilst holding down the shift key.

Select "As shown on screen" from the next dialog and click OK.

Now navigate to the cell (on Sheet2 in this example) where you want to show the dynamic picture and hit the paste button (or control-v):
screenshot showing the pasted picture

Figure 5, screenshot showing the pasted picture.

  • This picture -of course- is static. To make it dynamic, we can use a formula as the source of the picture.
  • Click on the picture so it is selected. Then click once in the formula bar:
    screenshot showing the pasted picture's formula bar

Figure 6, screenshot showing the pasted picture's formula bar.

  • Type the equal sign, followed by the name "Picture", as shown above and hit the enter key.
  • Now select cell A2 and type one of the descriptions from the list into that cell. The picture belonging to that description should appear.

Making the picture dynamic

To simplify the selection of the picture description, we can use Data, validation:

  • Select cell A2 and choose Data, Validation... from the menu.
  • From the top dropdown, select the List option
  • In the Source box enter this formula: =PictureList
  • If needed, fill in the fields on the other tabs too ("Input message" and "Error Alert")
  • Click OK:

 The Data Validation dialog

Now you can select from the available picture descriptions:

A Data Validation in-cell drop down

And we're done!

Download

Click here to download a sample file.

Other solutions

Bernie Deitrick came up with this nice sheet, which handles more pictures, but uses VBA


Comments

Showing last 8 comments of 179 in total (Show All Comments):

 


Comment by: Maria (19-7-2017 13:52:26) deeplink to this comment

I downloaded your sample it it works great.
I need to set up 3 drop downs like this on the same sheet, and I can't figure out how to copy the other 2 sets.
Please help : )


Comment by: Jan Karel Pieterse (13-8-2017 17:06:02) deeplink to this comment

Hi Maria,

You will have to create a set of range names for each dropdown, precisely as in the example, but each having different range names of course.


Comment by: Deepak (25-2-2018 06:10:07) deeplink to this comment

Hi,

I have created a ADD-ins and placed in excel ribbon using Custom UI Editor For Microsoft Office XML. When Click in Tab of this ADD-in it open a Excel DATA file and a Userform together, which take data that have been searched from this Excel file.

what i want is, Add-n should only open userform despite of open other excel data file, userform should take data from inbuilt workbook of ADD-in

i tried Application.isaddin true/false but it is not working!

When a workbook convert into addin by Application.isaddin true/false, no data can be taken from this hidden workbook of addin and userform is not taking data from this hidden workbook of ADDin


Comment by: Jan Karel Pieterse (26-2-2018 10:16:23) deeplink to this comment

Hi Deepak,

What happens when you click a custom ribbon button is set in the onaction callback of that button. The name of that onaction macro must be unique, otherwise you risk that the button may call an identically named macro in another workbook. So make sure your onaction callback has a unique name.


Comment by: DeePak (1-3-2018 06:46:24) deeplink to this comment

Hi,

I set addinn to open Excel Data file and userform together. Excel data file ( Named AD-details) has 1000 rows AD data which are being used by userform (Named: AD-search).

This userform takes data from this Workbook (AD-details) and showing result.

but as every add-inn has hidden workbook(sheets) and this 1000 rows data can be saved into it as well.

and i want when i click on onaction callback this must open AD-search Userform only and for its data, this userform should take data from workbook of Addinn itself





Comment by: Jan Karel Pieterse (1-3-2018 08:41:43) deeplink to this comment

Hi Deepak,

The reason your code does not work as expected is very hard to know without seeing your code. However, if your code addresses a different workbook than you want, make sure you inlude the workbook object reference in your code, e.g.:

instead of:

Worksheets("AD Data")

use:
ThisWorkbook.Worksheets("AD Data")


Comment by: frank hens (21-9-2023 16:39:00) deeplink to this comment

here you link fixed images to a location i excekl sheet but what if you want to link dynamic data between excel and word 2010?

i query data using sql in the excel sheet and one of these data put in cel A1 are paths to an image like e.g. C:\voorbeeld beeldkoppeling\Etikett 12er MiniCard Vollmilch.jpg

this path should be linked to a word template where i want to display that pathwayfrom A1 from the excel file not as a pathway but as an image with always the same image format.
after updating the data and thus teh pathway to the imagein the excelfile the image should also dynamicallly be updated in the wordtemplate.

how do i do this ?


Comment by: Jan Karel Pieterse (22-9-2023 10:58:00) deeplink to this comment

Hi Frank,

As far as I know this can only be achieved using automation. Such as VBA code, Office Script, a .NET Com add-in, Office Javascript or Power Automate.


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.




To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].