Help Calling Pix With Macro-Affects All Pix on Sheet

  • Thread starter Thread starter documike
  • Start date Start date
D

documike

I used some code provided by another helpful soul and it works
great....except, it affects all the pictures on my sheet. I've built a tool
with several graphics on it but only want one set of pictures to work with
the VLOOKUP and leave the rest of them alone. Now all my "Pictures"
disappear except for the one cell where I set up the VLOOKUP. How can I
exclude some of the other graphic images?
Here's the code I'm using...Thanks

Private Sub Worksheet_Calculate()
Dim oPic As Picture
Me.Pictures.Visible = False
With Range("P16")
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
 
You could name the pictures that should be hidden/shown nicely.

Give them a nice prefix (say DM_).

Then you can loop through all the pictures and hide just the ones you want.

Option Explicit
Private Sub Worksheet_Calculate()

Dim oPic As Picture
Dim myPfx As String

myPfx = "DM_"

For Each oPic In Me.Pictures
If LCase(Left(oPic.Name, 3)) = LCase(myPfx) Then
oPic.Visible = False
End If
Next oPic

With Me.Range("P16")
Set oPic = Nothing
On Error Resume Next
Set oPic = Me.Pictures(myPfx & .Text)
On Error GoTo 0

If oPic Is Nothing Then
'do nothing
Else
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
End If
End With
End Sub

You can rename your pictures by selecting them, then typing the new name in the
namebox (to the left of the formula bar). Don't forget to hit enter after you
type the new name.
 
_Dave, I tried the code...I tried naming a pic the following...all I got was
the name showing in P16
I used DM_1, DM_Picture 440, DM 2

It's got to be something simple...am I naming incorrectly? Thanks for the
code.
 
Dave, clarification...the picture I named DM_1 disappeared off sheet 1
(normal), but only DM_1 showed in P16. I named the picture DM_1 as you
mentioned (renaming and selecting Enter)
 
The code you're using shows the picture that has the name "DM_" & whatever is in
P16 of that sheet.

So what do you have in P16?
 
Dave, I'll include the code I'm using and the P16 VLOOKUP. The LOOKUP I'm
using worked fine when I started using the macro...but now instead of the
picture I just get the name DM_1 which is what's in the VLOOKUP Table row 21.
I'm keeping all the pictures on sheet 1 and the VLOOKUP table is on sheet 2

Here's the P16 code: =VLOOKUP(Control!B123,Control!A125:U147,21)

Here's the macro:
Option Explicit
Private Sub Worksheet_Calculate()

Dim oPic As Picture
Dim myPfx As String

myPfx = "DM_"

For Each oPic In Me.Pictures
If LCase(Left(oPic.Name, 3)) = LCase(myPfx) Then
oPic.Visible = False
End If
Next oPic

With Me.Range("P16")
Set oPic = Nothing
On Error Resume Next
Set oPic = Me.Pictures(myPfx & .Text)
On Error GoTo 0

If oPic Is Nothing Then
'do nothing
Else
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
End If
End With
End Sub
 
Does your =vlookup() return 1 or DM_1

The code adds the prefix to it. So if you made the =vlookup() return DM_1, the
code now looks for DM_DM_1.

So either remove the prefix from the what the formula returns or get rid of that
portion of the code:

Set oPic = Me.Pictures(myPfx & .Text)
may become:
Set oPic = Me.Pictures(.Text)


And one more minor change:
Change this:
If LCase(Left(oPic.Name, 3)) = LCase(myPfx) Then
to
If LCase(Left(oPic.Name, len(myPfx))) = LCase(myPfx) Then

If you change the prefix to something that isn't 3 characters long, it'll still
work ok.



Dave, I'll include the code I'm using and the P16 VLOOKUP. The LOOKUP I'm
using worked fine when I started using the macro...but now instead of the
picture I just get the name DM_1 which is what's in the VLOOKUP Table row 21.
I'm keeping all the pictures on sheet 1 and the VLOOKUP table is on sheet 2

Here's the P16 code: =VLOOKUP(Control!B123,Control!A125:U147,21)

Here's the macro:
Option Explicit
Private Sub Worksheet_Calculate()

Dim oPic As Picture
Dim myPfx As String

myPfx = "DM_"

For Each oPic In Me.Pictures
If LCase(Left(oPic.Name, 3)) = LCase(myPfx) Then
oPic.Visible = False
End If
Next oPic

With Me.Range("P16")
Set oPic = Nothing
On Error Resume Next
Set oPic = Me.Pictures(myPfx & .Text)
On Error GoTo 0

If oPic Is Nothing Then
'do nothing
Else
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
End If
End With
End Sub
Click to expand...
 
Dave, it's working beautifully! Thank you so much...I was putting the wrong
data in the Lookup cell (DM_1 vs 1). This is something I've been wanting to
do for a couple of years, so you have made me very happy! I've asked a lot
of people in the past and never been able to find anyone who had done
something similar.
 
Glad you got it working.


Dave, it's working beautifully! Thank you so much...I was putting the wrong
data in the Lookup cell (DM_1 vs 1). This is something I've been wanting to
do for a couple of years, so you have made me very happy! I've asked a lot
of people in the past and never been able to find anyone who had done
something similar.
Click to expand...
 
Back
Top