Display Image Based on Lookup Value

[mcmahobt]

Hello all,

I am relatively new to VBA, and hope this is an easy question for you experts out there to address. I am currently using the code supplied at McGimpsey & Associates : Excel : Display picture based on cell value to display a picture based on a cell value. The code is shown below:

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

I am wondering what the best way (if possible) of having this code be applied to a range of cells, say F1:F4, would be? I tried simply changing the F1 in the code to the range F1:F4, but it did not seem to work. Any help is appreciated!

 

[RickXL]

Hi,

Try this:

Private Sub Worksheet_Calculate()
    Dim oPic As Picture
    Dim rCell As Range
    Me.Pictures.Visible = False
    For Each rCell In Range("F1:F4")
        With rCell
            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
    Next rCell
End Sub

You just need to loop round the cells in the range.

 

[mcmahobt]

RickXL,

This is great. Thanks for the help, and sorry about my late response. However, I was wondering if you could answer two more (hopefully quick questions) to point me in the right direction...

1) What if I'm trying to apply this code to cells across columns? For example, instead of having the picture return values within cells F1:F4 only, could it be possible to apply it to cells F1:F4;G1:G4?

2) If a same value is entered in cells F1:F4, only one picture will appear, and the other cell(s) will just return the VLOOKUP of the picture's defined name. Any suggestions on altering this so that a multiple of one picture can be entered in multiple cells simultaneously?

Thanks again for the help!