Macro to display a picture

[JB Bates]

I saw a previous post that referred me to
Maybe something for you at John McGimpsey's site.

http://www.mcgimpsey.com/excel/lookuppics.html


and I am able to get this to work if i only have ONE picture that needs to
be displayed per worksheet. But i have the scenario there I look up the
pilot flying in A2 and need to display the photo in G2 for a range of rows
2:10

I altered the Marco to have a range of "a2:a10"
but i still only get a photo for the first row it finds. For all subsequent
rows the "formula" in G3 - G10 shows the correct correlating picture name but
no picture is displayed.

Any help would be greatly appreciated. THanks - JB

 

[Dave Peterson]

This is JE's code:

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

Maybe you can make sure your pictures are nicely named. Then check to see if
the name begins with the value in that cell (F1 in JE's code):

Private Sub Worksheet_Calculate()
Dim oPic As Picture
Me.Pictures.Visible = False
With Range("F1")
For Each oPic In Me.Pictures
If lcase(oPic.Name) like (.Text & "*" )Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
'Exit For 'don't leave, keep looking for more matching
names
End If
Next oPic
End With
End Sub

But you'll have to make sure that the names of the pictures begin with the value
in F1.