Hi David,

I've seen this problem before and I've managed to get it working again
by dimensioning the pictures as Shape objects rather than Picture
objects. I have no idea as to why the problem occurs nor why the change
in dimensioning should be a cure. With that in mind, I can't guarantee
that this solution will work in your case as it has in others.

In changing "Dim oPic As Picture" to "Dim shpPic As Shape", a
consideration that then needs to be made is that there are a lot of
shape objects that could be on your sheet and you wouldn't want the
code to have any effect on their visibility, eg the little drop down
arrow that indicates a cell with a drop down list is a Shape object,
and you won't want them disappearing on you!
So, to ensure that these other shapes are left alone, the code loop
first checks the Shape's Type property. If the Shape's Type is
included in the comma separated list of values in the Select Case then
it will become invisible if its name is not in the range "B14:B113" (I
have assumed that 100 cells starting from B14 will accommodate your
list of picture names)

If you're not sure of a Shape's Type value, then one way of finding it
is to select that shape, then in the Immediate Window of the Visual
Basic Editor type...

?Selection.ShapeRange.Type

then when you press Enter you should see its Type value appear.
Some examples of Shape Type values are...

AutoShapes = 1
Freeform = 5
Grouped Shapes = 6
Drop Down = 8
Line = 9
Picture = 13
TextBox = 17

I'm guessing, from the code you posted, that you have repeated the same
code steps for each picture. I have instead used two,nested loops.

One other change I made to the original code was to include the
possibility of having shapes whose type is included in the Select Case
but are still ignored because their Name starts with the Tilde
character "~". This means that if there is a particular picture (Type =
13) that you want to always be visible, then you can edit its name in
the Name box on the right of the Formula Bar so that the first
character is the tilde, eg if picture named Dog has its name changed to
~Dog then it will be ignored by the code and will always be visible.

I've also removed case sensitivity for the picture name, so that DOG,
dog, Dog etc will all show a picture whose name is Dog if that name is
in the B14:B113 cells


Private Sub Worksheet_Calculate()
Dim blnShow As Boolean
Dim sngTop As Single
Dim sngLeft As Single
Dim shpPic As Shape
Dim rngPicCell As Range
Dim rngPicRange As Range
Set rngPicRange = Me.Range("B14:B113")
'The above range allows for 100 cells
'for holding Pic names. Edit to suit
'your needs.
For Each shpPic In Me.Shapes
If Left(shpPic.Name, 1) < "~" Then
'if any shapes on your sheet need to be
'unaffected by the code then you will
'need to preface their name with the
'Tilde character. For example, say there
'is a Pic named "Logo"; if the text "Logo"
'is not in any of the cells in rngPicRange,
'then the code will render the Logo shape
'invisible. However, if it is renamed
'"~Logo" it will reman visible at all times.
For Each rngPicCell In rngPicRange
Select Case shpPic.Type
Case 1, 5, 6, 13
'only AutoShapes (1), Freeforms(5),
'Grouped shapes (6), and pictures (13)
'will have their visibility 'controlled.
'Edit to suit your needs
If UCase(rngPicCell.Value) = _
UCase(shpPic.Name) Then
blnShow = True
sngTop = rngPicCell.Top
sngLeft = rngPicCell.Offset(0, 1).Left
Exit For
End If
End Select
Next rngPicCell
If blnShow Then
With shpPic
.Visible = True
.Left = sngLeft
.Top = sngTop
End With
Else: shpPic.Visible = False
End If
blnShow = False
End If
Next shpPic
End Sub


Ken Johnson