If you add pictures to an Excel workbook, the file size can increase pretty quickly. And if you’re updating the pictures occasionally, perhaps for a product catalogue, you’d have to remember to update all the Excel files that have those pictures.
Instead of adding the pictures to the Excel file, Ron Coderre has created a sample workbook that displays pictures from a network file folder or even a web folder. You can distribute Excel workbooks with links to the picture files, and that will mean smaller files, and easier updates
Enter the Picture File Info
In the Excel workbook where you want the pictures, create a list of picture names, with file path and file names in the adjacent column. In the example shown below, two files are in the C drive, and one is on the internet.
In Ron’s sample file, the list of picture file names is in a range named LU_DisplayName. The picture names and file locations are in a range named LU_Name_FileLoc_XRef.
Select a Picture
Using data validation, Ron created a drop down list where users can select one of the picture file names. The data validation cell is named rngDisplayName.
A VLOOKUP formula returns the location of the selected picture file, in a cell named rngFileLocation.
The selected picture is displayed in a range named rngPicDisplayCells.
How It Works
To make the selected picture show on the worksheet, Ron added some event code to the worksheet. When the data validation cells changes, the code runs, and shows the selected picture file.
In Ron’s sample file, you can view the detailed instructions for setting up the workbook and displaying the pictures. He describes the data validation setup, the named range formulas and the VBA code to make everything work.
Download the Sample File
To see how the picture display works, you can download Ron’s sample file from the Contextures website.
In the “Charts and Graphs” section, look for “RCH0002 – Insert Pictures from Folder”.
The file contains macros, so you’ll have to enable them to test the file. There are two versions of the file — one for Excel 2007 an done for earlier versions. Both sample files are zipped.
RCH0002 – Insert Pictures from Folder