Home

General XL Tips

XL Formulae

Using VBA with XL

XL VBA Functions

XL VBA Macros

Mac XL

This site will look better if you upgrade to a browser that supports web standards.

Looking up and displaying a picture based on a cell value

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.

Valid XHTML 1.1 Valid CSS Made on a Macintosh

Quick Links

Unfamilar with macros? See David McRitchie's Getting Started with Macros and User Defined Functions.

Where to put event macros.