Attn: Dave P. Question re Pix Calls via Macro

[Guest]

Dave, the solution you provided is working wonderfully...of course now I have
to stretch the envelope again! I was wondering, if I have another cell on
the same sheet where I want to put another set of graphics called via a
VLOOKUP as before, what code do I put in the first sheet since I can't use
the same VLOOKUP as the first one. I tried plugging the same code into sheet
a second time, only specifying the second cell location & VLOOKUP code but it
faults on the second "Worksheet_Calculation liine.

 

[Dave Peterson]

You only get one of those worksheet_calculate events per worksheet.

So if you want your routine to do more, you have to put it into that same
procedure.

I'm guessing that you're going to use more pictures with another unique prefix.

I chose DM01_ and DM02_ for my prefixes. (But you could use whatever you
want/have.)

And I've forgotten if you embedded the value into the cell or combine it in
code. But this might get you closer.



Option Explicit
Private Sub Worksheet_Calculate()

Dim oPic As Picture
Dim myPrefixes As Variant
Dim myAddresses As Variant
Dim iCtr As Long

myPrefixes = Array("DM01_", "DM02_")
myAddresses = Array("P16", "Q16")

For Each oPic In Me.Pictures
For iCtr = LBound(myPrefixes) To UBound(myPrefixes)
If LCase(Left(oPic.Name, Len(myPrefixes(iCtr)))) _
= LCase(myPrefixes(iCtr)) Then
With Me.Range(myAddresses(iCtr))
If LCase(oPic.Name) = LCase(.Text) Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Else
oPic.Visible = False
End If
End With
End If
Next iCtr
Next oPic

End Sub