Pic Macro
 

Hi!
How can I modify this code to work on any pic_ in the worksheet
(dozens) without having to enter each one individulally?
Anytime the active cell = the pic_"name" the picture appears.
When I select a name from a validation list in cell A1 I have to hit
enter in order for the correct picture to appear. Why?

<Start Code
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim shp As Shape
On Error GoTo ws_exit
Application.EnableEvents = False
Me.Shapes("pic_Kids").Visible = False
Me.Shapes("pic_Tractor").Visible = False
Me.Shapes("pic_Krause").Visible = False
If Target.Address = "$A$1" Then
Me.Shapes("pic_Kids").Visible = False
Me.Shapes("pic_Tractor").Visible = False
Me.Shapes("pic_Krause").Visible = False
End If
On Error Resume Next
Me.Shapes("pic_" & Target.Value).Visible = True
ws_exit:
Application.EnableEvents = True
On Error GoTo 0
End Sub
<End Code

Any help would be appriciated!
Have a great holiday!!


--
Brian Matlack
------------------------------------------------------------------------
Brian Matlack's Profile: http://www.excelforum.com/member.php...fo&userid=3508
View this thread: http://www.excelforum.com/showthread...hreadid=557481

Pic Macro
 

Have a look at this article for a non VBA approach.

http://www.jkp-ads.com/Articles/ShowPicture00.htm

Cheers
Andy

Brian Matlack wrote:
Hi!
How can I modify this code to work on any pic_ in the worksheet
(dozens) without having to enter each one individulally?
Anytime the active cell = the pic_"name" the picture appears.
When I select a name from a validation list in cell A1 I have to hit
enter in order for the correct picture to appear. Why?

<Start Code
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim shp As Shape
On Error GoTo ws_exit
Application.EnableEvents = False
Me.Shapes("pic_Kids").Visible = False
Me.Shapes("pic_Tractor").Visible = False
Me.Shapes("pic_Krause").Visible = False
If Target.Address = "$A$1" Then
Me.Shapes("pic_Kids").Visible = False
Me.Shapes("pic_Tractor").Visible = False
Me.Shapes("pic_Krause").Visible = False
End If
On Error Resume Next
Me.Shapes("pic_" & Target.Value).Visible = True
ws_exit:
Application.EnableEvents = True
On Error GoTo 0
End Sub
<End Code

Any help would be appriciated!
Have a great holiday!!



--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Pic Macro
 

Try something like this...

Dim shp As Shape

For Each shp In Shapes
shp.Visible = msoFalse
Next shp

--
HTH...

Jim Thomlinson


"Brian Matlack" wrote:


Hi!
How can I modify this code to work on any pic_ in the worksheet
(dozens) without having to enter each one individulally?
Anytime the active cell = the pic_"name" the picture appears.
When I select a name from a validation list in cell A1 I have to hit
enter in order for the correct picture to appear. Why?

<Start Code
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim shp As Shape
On Error GoTo ws_exit
Application.EnableEvents = False
Me.Shapes("pic_Kids").Visible = False
Me.Shapes("pic_Tractor").Visible = False
Me.Shapes("pic_Krause").Visible = False
If Target.Address = "$A$1" Then
Me.Shapes("pic_Kids").Visible = False
Me.Shapes("pic_Tractor").Visible = False
Me.Shapes("pic_Krause").Visible = False
End If
On Error Resume Next
Me.Shapes("pic_" & Target.Value).Visible = True
ws_exit:
Application.EnableEvents = True
On Error GoTo 0
End Sub
<End Code

Any help would be appriciated!
Have a great holiday!!


--
Brian Matlack
------------------------------------------------------------------------
Brian Matlack's Profile: http://www.excelforum.com/member.php...fo&userid=3508
View this thread: http://www.excelforum.com/showthread...hreadid=557481