Help to make a single picture visible when all pictures are set to be hidden

[Gowen]

Hi

I am new to the site and a bit of a VBA noob, any help is appreciated.

I am trying to create a sheet that will alllow pictures to be shown when a specific option is chosen from a drop down list.

I have used the following to achieve this and it works fine -

Code:

Private Sub Worksheet_Calculate()
Dim oPic As Picture
Me.Pictures.Visible = False
With Range("B18")
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

What this also does is hide all pictures on the worksheet, as I understand this needs to happen for the drop down selection to work.

The problem I am having is that with all pictures now hidden my top banner image has gone also and I cannot seem to write a piece of code that will allow me to hide all pictures as per the above but show one specific image constantly.

- I have tried adding the following to the above code along with many other failed efforts -


Code:

Private Sub Worksheet_Calculate2()
ActiveSheet.Shapes("Picture 24").Visible = True
End Sub

this is not working and having tried several different methods and all failing miserably I am hoping for a few pointers.....


Many thanks

Paul

[6StringJazzer]

Hi Paul,

First, you should be aware the the board requires VBA code to be enclosed in code tags, like my example below. Hopefully the advantage will look obvious.

I don't understand what you are trying to do with your second block of code, because adding a 2 at the end of that Sub name makes it just like any other Sub, and it won't execute unless you call it specifically.

Have you tried something like this:

Please Login or Register  to view this content.

Further, note that your code will execute any time there is a recalc on the page, not just when the dropdown changes. To specifically address the dropdown I would do this:

Please Login or Register  to view this content.