Show Picture

JKP Application Development Services.

Microsoft Office Application Development

Show Picture

Up • Defined Names • Corrupt Files • Create Addins • Startup Problems • Chart an Equation • Show Picture • Round2Digits • Control Events • Custom Find • FormulaWrapper • Disable Events • Workbook Open Bug • WebQuery • Register UDFs • Undo With Excel VBA • Select a range (VBA) • Transpose Table • Docking VBE Windows • Excel 2007 Tables • Excel 2007 Tables (VBA) • Update An Addin • Addin Installation • Object Lister • Excel 2007 FileFormat • Catch Paste • Listbox AutoSize • Fix Links to UDFs

• •

Search

Zoeken

Go to	Ga Naar
Home Up

Donate	Donaties
Did you find something helpful on my site? Consider a donation! Heeft u iets gevonden waar u wat aan had? Overweeg dan een donatie!

Showing a picture from a list of pictures

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

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

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

Creating the dynamic picture

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

Figure 2, 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):

Figure 3, 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:

Figure 4, 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.

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: