This is fantastic
This will really help me !!
Can this be done across three drop down list?
Thanx Gary's Student
Assuming 12 pictures named Picture 1 through to Picture 12.
Pictures 1 to 4 to be shown/hidden at C1, Pictures 5 to 8 to be shown/
hidden at F1 and Pictures 9 to 12 to be shown/hidden at I1.
Each of your three data validation drop downs refer to their own
separate VLOOKUP table.
Then, the following code might be what you are wanting to do...
Private Sub Worksheet_Calculate()
Dim oPic As Picture
Dim ncPix1 As New Collection
Dim ncPix2 As New Collection
Dim ncPix3 As New Collection
For Each oPic In Me.Pictures
Select Case oPic.Name
Case "Picture 1", "Picture 2", _
"Picture 3", "Picture 4"
ncPix1.Add Item:=oPic
Case "Picture 5", "Picture 6", _
"Picture 7", "Picture 8"
ncPix2.Add Item:=oPic
Case "Picture 9", "Picture 10", _
"Picture 11", "Picture 12"
ncPix3.Add Item:=oPic
End Select
Next oPic
Me.Pictures.Visible = False
With Range("C1")
For Each oPic In ncPix1
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("F1")
For Each oPic In ncPix2
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("I1")
For Each oPic In ncPix3
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
Next oPic
End With
End Sub
It's just a tripling of the original code after all the Pictures on
the sheet have been added to three new collections so that they can be
referred to separately without affecting the visibility of Pictures in
other collections.
Ken Johnson
