Author: Oscar Cronquist Article last updated on January 21, 2020

How to change a picture in a worksheet dynamically

Rahul asks:

I want to know how to create a vlookup sheet, and when we enter a name in a cell Excel shows all the details and a photo.

The formula in cell C4 uses the value in cell C2 to lookup the correct region specified in the "Data" worksheet. You can easily add more details using this approach for example in cell D4.

Formula in cell C4:

=INDEX(Data!B2:B9,MATCH(C2,Data!A2:A9,0))

The MATCH function looks for a matching value in cell range A2:A9 in worksheet Data using condition specified in cell C2 located on worksheet "Lookup".

MATCH(C2,Data!A2:A9,0)

becomes

MATCH("Giraffe", {"Giraffe"; "Rhino"; "Elephant"; "Buffalo"; "Lion"; "Tiger"; "Hippo"; "Cheetah"}, 0)

The third argument is 0 (zero) meaning there must be an exact match or the function returns an error.

MATCH("Giraffe", {"Giraffe"; "Rhino"; "Elephant"; "Buffalo"; "Lion"; "Tiger"; "Hippo"; "Cheetah"}, 0)

returns 1. The value "Giraffe" is the first value in the array.

The INDEX function returns a value from cell range B2:B9 in worksheet Data using a row argument which we calculated in the previous step.

INDEX(Data!B2:B9,MATCH(C2,Data!A2:A9,0))

becomes

INDEX(Data!B2:B9, 1)

becomes

INDEX({"Africa"; "Africa"; "Africa, Asia"; "Africa"; "Africa, Asia"; "Africa, Asia"; "Africa"; "Africa"}, 1)

and returns "Africa" in cell C4.

The following formula in cell C6 extracts the corresponding file path and file name, however, the value is hidden with the use of cell formatting.

Formula in cell C6:

=INDEX(Data!C2:C9,MATCH(C2,Data!A2:A9,0))

I have hidden the contents in cell C6.

  1. Select cell C6.
  2. Press Ctrl + 1 to open the cell formatting dialog box.
  3. Click Custom
  4. Type ;;;
  5. Click OK button.

Data sheet

change picture - data sheet

The Data worksheet contains the item names in column A, the details in column B and the file name and file path in column C.

VBA code

'Event code
Private Sub Worksheet_Change(ByVal Target As Range)

'Check if cell C2 has been changed
If Target.Address = "$C$2" Then

    'Delete the previous image
    Shapes(1).Delete

    'Insert and select image specified in cell C6 
    Pictures.Insert(ActiveSheet.Range("C6").Value).Select

    'The With ... End With statement allows you to write shorter code by referring to an object only once instead of using it with each property.
    With Selection

        'Change image height to 120.24 points
        .Height = 120.24

        'Change image width to 180 points
        .Width = 180

        'Position image 96.75 points from left border of Excel worksheet
        .Left = 96.75

        'Position image 90 points from top border of Excel worksheet
        .Top = 90
    End With

    'Select cell C3 of active worksheet
    Range("C3").Select
End If
End Sub

The VBA code above is event code meaning it is executed if a cell value changes. Event code is put in a worksheet or a workbook module and not in a regular module, there are detailed instructions below on where to put the code.

The VBA code above is not going to work great if you have other shapes or images placed in the same worksheet.

The following line deletes the first image in the active worksheet and if you have more images the code may delete one of those images which is not ideal.

Shapes(1).Delete

The workaround is to save the image name to another cell which is then used to remove the old image next time the code is executed. This prevents multiple images being inserted on top of each other and keeps the workbook size smaller.

If there is no value in cell D6 the code will error out, to prevent that from happening I have enabled error handling while image being deleted.

'Event code
Private Sub Worksheet_Change(ByVal Target As Range)

'Check if cell C2 has been changed
If Target.Address = "$C$2" Then

    'Enable error handling
    On Error Resume Next
     
       'Delete image using the name saved in cell D6
       ActiveSheet.Shapes(Range("D6")).Delete

    'Disable error handling
    On Error GoTo 0

    'Insert and select image specified in cell C6 
    ActiveSheet.Pictures.Insert(ActiveSheet.Range("C6").Value).Select

    'The With ... End With statement allows you to write shorter code by referring to an object only once instead of using it with each property.
    With Selection

        'Change image height to 120.24 points
        .Height = 120.24

        'Change image width to 180 points
        .Width = 180

        'Position image 96.75 points from left border of Excel worksheet
        .Left = 96.75

        'Position image 90 points from top border of Excel worksheet
        .Top = 90

        'Save image name to cell D6
        Range("D6") = .Name
    End With
    
    'Select cell C3
    Range("C3").Select
End If
End Sub

Make sure you apply cell formatting to cell D6 as well.

Where to put the VBA event code?

How to change a picture in a worksheet dynamically event code1

  1. Right click on the worksheet name.
  2. Click "View Code".
    How to change a picture in a worksheet dynamically event code 1
  3. Paste code below to sheet module.
  4. Exit VB editor.
Note, make sure to save your workbook with file extension *.xlsm in order to keep the code attached to the workbook.

Animated image

changepicture

Download Excel file


Vlookup-pictures.zip