This site will look better if you upgrade to a browser that supports web standards.
This site will look better if you upgrade to a browser that supports web standards.
McGimpsey & Associates This site will look better if you upgrade to a browser that supports web standards.
At times, pictures are worth 1,000 words, and it's desirable to show a picture in a cell that changes depending on information in another cell. Since pictures in XL can't be placed in a cell, but rather float on top of the sheet, and since lookup functions return values, not pictures, some VBA is required to simulate looking up a picture and placing it in a cell. Note that for large numbers of pictures (like, say, a parts catalog), a database solution would likely be much easier and much more efficient.
As an example, say a partnership consisting of Bob, Ted, Carol and Alice wants to have the lead partner's picture on a bid sheet. The lead partner is chosen in a dropdown in cell A2 and the picture is to be displayed on top of cell F1. On a separate sheet, a lookup table, named PicTable associates the name of the partner and the corresponding picture name.
The pictures are placed anywhere on Sheet1. In cell F1 where the picture is to reside, this formula is entered:
=VLOOKUP(A2, PicTable, 2, False)
The Worksheet_Calculation event is then used to place the picture on top of cell F1, hiding the formula. The event macro is placed in the worksheet code module, where it will fire each time a calculation occurs on the sheet. The macro hides all the pictures, then displays the one corresponding to the value in F1, repositioning it if necessary.
Private Sub Worksheet_Calculate()
Dim oPic As Picture
Me.Pictures.Visible = False
With Range("F1")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
Next oPic
End With
End Sub
This is what the sheet will look like after "Carol" is chosen in A2:
Download a sample workbook using this technique.
This page last updated
© Copyright 2001 - 2004 McGimpsey and Associates. Except where noted, all code on this site may be distributed under the Gnu GPL. Acknowledgement is appreciated.
Unfamilar with macros? See David McRitchie's Getting Started with Macros and User Defined Functions.