software

I built a code to hide and unhide Bit map shapes. The shapes are quot;stackedquot; on
each other and the code is suppose to turn each one on when it's quot;namequot;
appears in a cell. In this case B44. The quot;namesquot; appear below in quot;quot;. The
problem is that the program just runs through the shapes over and over
instead of stopping on the shape named in the line.

What am I doing wrong?

Thanks!
GW

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim iCtr As Long
Dim mySfx As Long
Dim myShape As Shape

If Intersect(Target, Range(quot;B44quot;)) Is Nothing Then Exit Sub

For iCtr = 1 To Me.Shapes.Count
Me.Shapes(quot;shapequot; amp; iCtr).Visible = False
Next iCtr

With Target
Select Case UCase(.Value)
Case Is = quot;J-Frame/Belt/Bolt-inquot;: mySfx = 1
Case Is = quot;J-Frame/Belt/Weld-inquot;: mySfx = 2
Case Is = quot;J-Frame/Belt/Bolt-in/Integral Bafflesquot;: mySfx = 3
Case Is = quot;J-Frame/Belt/Bolt-in/Integral Baffles/Pillowquot;: mySfx
= 4
Case Is = quot;J-Frame/Belt/Bolt-in/Single Break Bafflequot;: mySfx = 5
Case Is = quot;J-Frame/Belt/Bolt-in/Single Break Baffle/Pillowquot;:
mySfx = 6
Case Is = quot;J-Frame/Belt/Bolt-in/Double Break Bafflequot;: mySfx = 7
Case Is = quot;J-Frame/Belt/Bolt-in/Double Break Baffle/Pillowquot;:
mySfx = 8
Case Is = quot;J-Frame/Belt/Weld-in/Integral Bafflequot;: mySfx = 9
Case Is = quot;J-Frame/Belt/Weld-in/Integral Baffle/Pillowquot;: mySfx =
10
Case Is = quot;J-Frame/Belt/Weld-in/Single Break Bafflequot;: mySfx = 11
Case Is = quot;J-Frame/Belt/Weld-in/Single Break Baffle/Pillowquot;:
mySfx = 12
Case Is = quot;J-Frame/Belt/Weld-in/Double Break Bafflequot;: mySfx = 13
Case Is = quot;J-Frame/Belt/Weld-in/Double Break Baffle/Pillowquot;:
mySfx = 14
Case Is = quot;Downward Angle/Belt/Inward/Bolt-inquot;: mySfx = 15
Case Is = quot;Downward Angle/Belt/Inward/Weld-inquot;: mySfx = 16
Case Is = quot;Downward Angle/Belt/Inward/Bolt-in/Single Break
Bafflequot;: mySfx = 17
Case Is = quot;Downward Angle/Belt/Inward/Bolt-in/Double Break
Bolt-in Bafflequot;: mySfx = 18
Case Is = quot;Downward Angle/Belt/Inward/Weld-in/Single Break
Bafflequot;: mySfx = 19
Case Is = quot;Downward Angle/Belt/Inward/Weld-in/Double Break
Bafflequot;: mySfx = 20
Case Is = quot;Angle/Flangequot;: mySfx = 21
Case Is = quot;Angle/Flange/Single Break Bafflequot;: mySfx = 22
Case Is = quot;Angle/Flange/Double Break Bafflequot;: mySfx = 23
Case Is = quot;Angle/Flange/Single Break Bolt-in Bafflequot;: mySfx = 24
Case Is = quot;Angle/Flange/Double Break Bolt-in Bafflequot;: mySfx = 25
Case Is = quot;Angle/Belt/Outward/Weld-inquot;: mySfx = 26
Case Is = quot;Angle/Belt/Outward/Bolt-inquot;: mySfx = 27
Case Is = quot;Angle/Belt/Inward/Weld-inquot;: mySfx = 28
Case Is = quot;Angle/Belt/Inward/Bolt-inquot;: mySfx = 29
Case Is = quot;Angle/Belt/Inward/Weld-in/Integral Bafflesquot;: mySfx = 30
Case Is = quot;Angle/Belt/Inward/Bolt-in/Integral Bafflesquot;: mySfx = 31
Case Is = quot;Channel/Belt/Outward/Weld-inquot;: mySfx = 32
Case Is = quot;Channel/Belt/Outward/Weld-in/Single Break Bafflequot;:
mySfx = 33
Case Is = quot;Channel/Belt/Outward/Weld-in/Double Break Bafflequot;:
mySfx = 34
Case Is = quot;External Mount Stud Bars/Beltquot;: mySfx = 35
Case Is = quot;Internal Mount Stud Bars/Beltquot;: mySfx = 36
Case Is = quot;Internal Clamp Designquot;: mySfx = 37
End Select
End With

If mySfx gt; 0 Then
On Error Resume Next
Set myShape = Me.Shapes(quot;shapequot; amp; mySfx)
On Error GoTo 0
If myShape Is Nothing Then
MsgBox quot;error in design--see Garyquot;
Else
myShape.Visible = True
End If
End If

End Sub

This line:

Select Case UCase(.Value)

means that you have to compare the .value to upper case:
case is = quot;J-FRAME/BELT/BOLT-INquot;
not
case is = quot;J-Frame/Belt/Bolt-inquot;

I think I'd weasel out of this and do this:

At the top:

Option Explicit
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)

then...
Select Case .Value

Then you'll be doing the same kind of comparison as excel does (ignoring case).

You can actually keep:
Select Case UCase(.Value)
But it may cause confusion--to you, not excel!

Cell Mate wrote:
gt;
gt; I built a code to hide and unhide Bit map shapes. The shapes are quot;stackedquot; on
gt; each other and the code is suppose to turn each one on when it's quot;namequot;
gt; appears in a cell. In this case B44. The quot;namesquot; appear below in quot;quot;. The
gt; problem is that the program just runs through the shapes over and over
gt; instead of stopping on the shape named in the line.
gt;
gt; What am I doing wrong?
gt;
gt; Thanks!
gt; GW
gt;
gt; Option Explicit
gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt;
gt; Dim iCtr As Long
gt; Dim mySfx As Long
gt; Dim myShape As Shape
gt;
gt; If Intersect(Target, Range(quot;B44quot;)) Is Nothing Then Exit Sub
gt;
gt; For iCtr = 1 To Me.Shapes.Count
gt; Me.Shapes(quot;shapequot; amp; iCtr).Visible = False
gt; Next iCtr
gt;
gt; With Target
gt; Select Case UCase(.Value)
gt; Case Is = quot;J-Frame/Belt/Bolt-inquot;: mySfx = 1
gt; Case Is = quot;J-Frame/Belt/Weld-inquot;: mySfx = 2
gt; Case Is = quot;J-Frame/Belt/Bolt-in/Integral Bafflesquot;: mySfx = 3
gt; Case Is = quot;J-Frame/Belt/Bolt-in/Integral Baffles/Pillowquot;: mySfx
gt; = 4
gt; Case Is = quot;J-Frame/Belt/Bolt-in/Single Break Bafflequot;: mySfx = 5
gt; Case Is = quot;J-Frame/Belt/Bolt-in/Single Break Baffle/Pillowquot;:
gt; mySfx = 6
gt; Case Is = quot;J-Frame/Belt/Bolt-in/Double Break Bafflequot;: mySfx = 7
gt; Case Is = quot;J-Frame/Belt/Bolt-in/Double Break Baffle/Pillowquot;:
gt; mySfx = 8
gt; Case Is = quot;J-Frame/Belt/Weld-in/Integral Bafflequot;: mySfx = 9
gt; Case Is = quot;J-Frame/Belt/Weld-in/Integral Baffle/Pillowquot;: mySfx =
gt; 10
gt; Case Is = quot;J-Frame/Belt/Weld-in/Single Break Bafflequot;: mySfx = 11
gt; Case Is = quot;J-Frame/Belt/Weld-in/Single Break Baffle/Pillowquot;:
gt; mySfx = 12
gt; Case Is = quot;J-Frame/Belt/Weld-in/Double Break Bafflequot;: mySfx = 13
gt; Case Is = quot;J-Frame/Belt/Weld-in/Double Break Baffle/Pillowquot;:
gt; mySfx = 14
gt; Case Is = quot;Downward Angle/Belt/Inward/Bolt-inquot;: mySfx = 15
gt; Case Is = quot;Downward Angle/Belt/Inward/Weld-inquot;: mySfx = 16
gt; Case Is = quot;Downward Angle/Belt/Inward/Bolt-in/Single Break
gt; Bafflequot;: mySfx = 17
gt; Case Is = quot;Downward Angle/Belt/Inward/Bolt-in/Double Break
gt; Bolt-in Bafflequot;: mySfx = 18
gt; Case Is = quot;Downward Angle/Belt/Inward/Weld-in/Single Break
gt; Bafflequot;: mySfx = 19
gt; Case Is = quot;Downward Angle/Belt/Inward/Weld-in/Double Break
gt; Bafflequot;: mySfx = 20
gt; Case Is = quot;Angle/Flangequot;: mySfx = 21
gt; Case Is = quot;Angle/Flange/Single Break Bafflequot;: mySfx = 22
gt; Case Is = quot;Angle/Flange/Double Break Bafflequot;: mySfx = 23
gt; Case Is = quot;Angle/Flange/Single Break Bolt-in Bafflequot;: mySfx = 24
gt; Case Is = quot;Angle/Flange/Double Break Bolt-in Bafflequot;: mySfx = 25
gt; Case Is = quot;Angle/Belt/Outward/Weld-inquot;: mySfx = 26
gt; Case Is = quot;Angle/Belt/Outward/Bolt-inquot;: mySfx = 27
gt; Case Is = quot;Angle/Belt/Inward/Weld-inquot;: mySfx = 28
gt; Case Is = quot;Angle/Belt/Inward/Bolt-inquot;: mySfx = 29
gt; Case Is = quot;Angle/Belt/Inward/Weld-in/Integral Bafflesquot;: mySfx = 30
gt; Case Is = quot;Angle/Belt/Inward/Bolt-in/Integral Bafflesquot;: mySfx = 31
gt; Case Is = quot;Channel/Belt/Outward/Weld-inquot;: mySfx = 32
gt; Case Is = quot;Channel/Belt/Outward/Weld-in/Single Break Bafflequot;:
gt; mySfx = 33
gt; Case Is = quot;Channel/Belt/Outward/Weld-in/Double Break Bafflequot;:
gt; mySfx = 34
gt; Case Is = quot;External Mount Stud Bars/Beltquot;: mySfx = 35
gt; Case Is = quot;Internal Mount Stud Bars/Beltquot;: mySfx = 36
gt; Case Is = quot;Internal Clamp Designquot;: mySfx = 37
gt; End Select
gt; End With
gt;
gt; If mySfx gt; 0 Then
gt; On Error Resume Next
gt; Set myShape = Me.Shapes(quot;shapequot; amp; mySfx)
gt; On Error GoTo 0
gt; If myShape Is Nothing Then
gt; MsgBox quot;error in design--see Garyquot;
gt; Else
gt; myShape.Visible = True
gt; End If
gt; End If
gt;
gt; End Sub

--

Dave Peterson



quot;Dave Petersonquot; wrote:

gt; This line:
gt;
gt; Select Case UCase(.Value)
gt;
gt; means that you have to compare the .value to upper case:
gt; case is = quot;J-FRAME/BELT/BOLT-INquot;
gt; not
gt; case is = quot;J-Frame/Belt/Bolt-inquot;
gt;
gt; I think I'd weasel out of this and do this:
gt;
gt; At the top:
gt;
gt; Option Explicit
gt; Option Compare Text
gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt;
gt; then...
gt; Select Case .Value
gt;
gt; Then you'll be doing the same kind of comparison as excel does (ignoring case).
gt;
gt; You can actually keep:
gt; Select Case UCase(.Value)
gt; But it may cause confusion--to you, not excel!
gt;
gt;
gt;
gt;
gt; Cell Mate wrote:
gt; gt;
gt; gt; I built a code to hide and unhide Bit map shapes. The shapes are quot;stackedquot; on
gt; gt; each other and the code is suppose to turn each one on when it's quot;namequot;
gt; gt; appears in a cell. In this case B44. The quot;namesquot; appear below in quot;quot;. The
gt; gt; problem is that the program just runs through the shapes over and over
gt; gt; instead of stopping on the shape named in the line.
gt; gt;
gt; gt; What am I doing wrong?
gt; gt;
gt; gt; Thanks!
gt; gt; GW
gt; gt;
gt; gt; Option Explicit
gt; gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; gt;
gt; gt; Dim iCtr As Long
gt; gt; Dim mySfx As Long
gt; gt; Dim myShape As Shape
gt; gt;
gt; gt; If Intersect(Target, Range(quot;B44quot;)) Is Nothing Then Exit Sub
gt; gt;
gt; gt; For iCtr = 1 To Me.Shapes.Count
gt; gt; Me.Shapes(quot;shapequot; amp; iCtr).Visible = False
gt; gt; Next iCtr
gt; gt;
gt; gt; With Target
gt; gt; Select Case UCase(.Value)
gt; gt; Case Is = quot;J-Frame/Belt/Bolt-inquot;: mySfx = 1
gt; gt; Case Is = quot;J-Frame/Belt/Weld-inquot;: mySfx = 2
gt; gt; Case Is = quot;J-Frame/Belt/Bolt-in/Integral Bafflesquot;: mySfx = 3
gt; gt; Case Is = quot;J-Frame/Belt/Bolt-in/Integral Baffles/Pillowquot;: mySfx
gt; gt; = 4
gt; gt; Case Is = quot;J-Frame/Belt/Bolt-in/Single Break Bafflequot;: mySfx = 5
gt; gt; Case Is = quot;J-Frame/Belt/Bolt-in/Single Break Baffle/Pillowquot;:
gt; gt; mySfx = 6
gt; gt; Case Is = quot;J-Frame/Belt/Bolt-in/Double Break Bafflequot;: mySfx = 7
gt; gt; Case Is = quot;J-Frame/Belt/Bolt-in/Double Break Baffle/Pillowquot;:
gt; gt; mySfx = 8
gt; gt; Case Is = quot;J-Frame/Belt/Weld-in/Integral Bafflequot;: mySfx = 9
gt; gt; Case Is = quot;J-Frame/Belt/Weld-in/Integral Baffle/Pillowquot;: mySfx =
gt; gt; 10
gt; gt; Case Is = quot;J-Frame/Belt/Weld-in/Single Break Bafflequot;: mySfx = 11
gt; gt; Case Is = quot;J-Frame/Belt/Weld-in/Single Break Baffle/Pillowquot;:
gt; gt; mySfx = 12
gt; gt; Case Is = quot;J-Frame/Belt/Weld-in/Double Break Bafflequot;: mySfx = 13
gt; gt; Case Is = quot;J-Frame/Belt/Weld-in/Double Break Baffle/Pillowquot;:
gt; gt; mySfx = 14
gt; gt; Case Is = quot;Downward Angle/Belt/Inward/Bolt-inquot;: mySfx = 15
gt; gt; Case Is = quot;Downward Angle/Belt/Inward/Weld-inquot;: mySfx = 16
gt; gt; Case Is = quot;Downward Angle/Belt/Inward/Bolt-in/Single Break
gt; gt; Bafflequot;: mySfx = 17
gt; gt; Case Is = quot;Downward Angle/Belt/Inward/Bolt-in/Double Break
gt; gt; Bolt-in Bafflequot;: mySfx = 18
gt; gt; Case Is = quot;Downward Angle/Belt/Inward/Weld-in/Single Break
gt; gt; Bafflequot;: mySfx = 19
gt; gt; Case Is = quot;Downward Angle/Belt/Inward/Weld-in/Double Break
gt; gt; Bafflequot;: mySfx = 20
gt; gt; Case Is = quot;Angle/Flangequot;: mySfx = 21
gt; gt; Case Is = quot;Angle/Flange/Single Break Bafflequot;: mySfx = 22
gt; gt; Case Is = quot;Angle/Flange/Double Break Bafflequot;: mySfx = 23
gt; gt; Case Is = quot;Angle/Flange/Single Break Bolt-in Bafflequot;: mySfx = 24
gt; gt; Case Is = quot;Angle/Flange/Double Break Bolt-in Bafflequot;: mySfx = 25
gt; gt; Case Is = quot;Angle/Belt/Outward/Weld-inquot;: mySfx = 26
gt; gt; Case Is = quot;Angle/Belt/Outward/Bolt-inquot;: mySfx = 27

gt; gt; Case Is = quot;Angle/Belt/Inward/Weld-inquot;: mySfx = 28
gt; gt; Case Is = quot;Angle/Belt/Inward/Bolt-inquot;: mySfx = 29
gt; gt; Case Is = quot;Angle/Belt/Inward/Weld-in/Integral Bafflesquot;: mySfx = 30
gt; gt; Case Is = quot;Angle/Belt/Inward/Bolt-in/Integral Bafflesquot;: mySfx = 31
gt; gt; Case Is = quot;Channel/Belt/Outward/Weld-inquot;: mySfx = 32
gt; gt; Case Is = quot;Channel/Belt/Outward/Weld-in/Single Break Bafflequot;:
gt; gt; mySfx = 33
gt; gt; Case Is = quot;Channel/Belt/Outward/Weld-in/Double Break Bafflequot;:
gt; gt; mySfx = 34
gt; gt; Case Is = quot;External Mount Stud Bars/Beltquot;: mySfx = 35
gt; gt; Case Is = quot;Internal Mount Stud Bars/Beltquot;: mySfx = 36
gt; gt; Case Is = quot;Internal Clamp Designquot;: mySfx = 37
gt; gt; End Select
gt; gt; End With
gt; gt;
gt; gt; If mySfx gt; 0 Then
gt; gt; On Error Resume Next
gt; gt; Set myShape = Me.Shapes(quot;shapequot; amp; mySfx)
gt; gt; On Error GoTo 0
gt; gt; If myShape Is Nothing Then
gt; gt; MsgBox quot;error in design--see Garyquot;
gt; gt; Else
gt; gt; myShape.Visible = True
gt; gt; End If
gt; gt; End If
gt; gt;
gt; gt; End Sub
gt;
gt; --
gt;
gt; Dave Peterson
gt;

I made the changes but it still runs through the shapes over and over not
stopping on the name that appears in B44.

May I send you the entire file so you can see it in action?Cell Mate

Debuging suggest...

At the end of the Select Case, put a Case Else stmt...

Case Else
Msgbox quot;Value: quot; amp; .value amp; quot; - quot; amp; mySfx

Run the macro and see if a message pops up.

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.quot;Cell Matequot; wrote:

gt;
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; This line:
gt; gt;
gt; gt; Select Case UCase(.Value)
gt; gt;
gt; gt; means that you have to compare the .value to upper case:
gt; gt; case is = quot;J-FRAME/BELT/BOLT-INquot;
gt; gt; not
gt; gt; case is = quot;J-Frame/Belt/Bolt-inquot;
gt; gt;
gt; gt; I think I'd weasel out of this and do this:
gt; gt;
gt; gt; At the top:
gt; gt;
gt; gt; Option Explicit
gt; gt; Option Compare Text
gt; gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; gt;
gt; gt; then...
gt; gt; Select Case .Value
gt; gt;
gt; gt; Then you'll be doing the same kind of comparison as excel does (ignoring case).
gt; gt;
gt; gt; You can actually keep:
gt; gt; Select Case UCase(.Value)
gt; gt; But it may cause confusion--to you, not excel!
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt; Cell Mate wrote:
gt; gt; gt;
gt; gt; gt; I built a code to hide and unhide Bit map shapes. The shapes are quot;stackedquot; on
gt; gt; gt; each other and the code is suppose to turn each one on when it's quot;namequot;
gt; gt; gt; appears in a cell. In this case B44. The quot;namesquot; appear below in quot;quot;. The
gt; gt; gt; problem is that the program just runs through the shapes over and over
gt; gt; gt; instead of stopping on the shape named in the line.
gt; gt; gt;
gt; gt; gt; What am I doing wrong?
gt; gt; gt;
gt; gt; gt; Thanks!
gt; gt; gt; GW
gt; gt; gt;
gt; gt; gt; Option Explicit
gt; gt; gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; gt; gt;
gt; gt; gt; Dim iCtr As Long
gt; gt; gt; Dim mySfx As Long
gt; gt; gt; Dim myShape As Shape
gt; gt; gt;
gt; gt; gt; If Intersect(Target, Range(quot;B44quot;)) Is Nothing Then Exit Sub
gt; gt; gt;
gt; gt; gt; For iCtr = 1 To Me.Shapes.Count
gt; gt; gt; Me.Shapes(quot;shapequot; amp; iCtr).Visible = False
gt; gt; gt; Next iCtr
gt; gt; gt;
gt; gt; gt; With Target
gt; gt; gt; Select Case UCase(.Value)
gt; gt; gt; Case Is = quot;J-Frame/Belt/Bolt-inquot;: mySfx = 1
gt; gt; gt; Case Is = quot;J-Frame/Belt/Weld-inquot;: mySfx = 2
gt; gt; gt; Case Is = quot;J-Frame/Belt/Bolt-in/Integral Bafflesquot;: mySfx = 3
gt; gt; gt; Case Is = quot;J-Frame/Belt/Bolt-in/Integral Baffles/Pillowquot;: mySfx
gt; gt; gt; = 4
gt; gt; gt; Case Is = quot;J-Frame/Belt/Bolt-in/Single Break Bafflequot;: mySfx = 5
gt; gt; gt; Case Is = quot;J-Frame/Belt/Bolt-in/Single Break Baffle/Pillowquot;:
gt; gt; gt; mySfx = 6
gt; gt; gt; Case Is = quot;J-Frame/Belt/Bolt-in/Double Break Bafflequot;: mySfx = 7
gt; gt; gt; Case Is = quot;J-Frame/Belt/Bolt-in/Double Break Baffle/Pillowquot;:
gt; gt; gt; mySfx = 8
gt; gt; gt; Case Is = quot;J-Frame/Belt/Weld-in/Integral Bafflequot;: mySfx = 9
gt; gt; gt; Case Is = quot;J-Frame/Belt/Weld-in/Integral Baffle/Pillowquot;: mySfx =
gt; gt; gt; 10
gt; gt; gt; Case Is = quot;J-Frame/Belt/Weld-in/Single Break Bafflequot;: mySfx = 11
gt; gt; gt; Case Is = quot;J-Frame/Belt/Weld-in/Single Break Baffle/Pillowquot;:
gt; gt; gt; mySfx = 12
gt; gt; gt; Case Is = quot;J-Frame/Belt/Weld-in/Double Break Bafflequot;: mySfx = 13
gt; gt; gt; Case Is = quot;J-Frame/Belt/Weld-in/Double Break Baffle/Pillowquot;:
gt; gt; gt; mySfx = 14
gt; gt; gt; Case Is = quot;Downward Angle/Belt/Inward/Bolt-inquot;: mySfx = 15
gt; gt; gt; Case Is = quot;Downward Angle/Belt/Inward/Weld-inquot;: mySfx = 16
gt; gt; gt; Case Is = quot;Downward Angle/Belt/Inward/Bolt-in/Single Break
gt; gt; gt; Bafflequot;: mySfx = 17
gt; gt; gt; Case Is = quot;Downward Angle/Belt/Inward/Bolt-in/Double Break
gt; gt; gt; Bolt-in Bafflequot;: mySfx = 18
gt; gt; gt; Case Is = quot;Downward Angle/Belt/Inward/Weld-in/Single Break
gt; gt; gt; Bafflequot;: mySfx = 19
gt; gt; gt; Case Is = quot;Downward Angle/Belt/Inward/Weld-in/Double Break
gt; gt; gt; Bafflequot;: mySfx = 20
gt; gt; gt; Case Is = quot;Angle/Flangequot;: mySfx = 21
gt; gt; gt; Case Is = quot;Angle/Flange/Single Break Bafflequot;: mySfx = 22
gt; gt; gt; Case Is = quot;Angle/Flange/Double Break Bafflequot;: mySfx = 23
gt; gt; gt; Case Is = quot;Angle/Flange/Single Break Bolt-in Bafflequot;: mySfx = 24
gt; gt; gt; Case Is = quot;Angle/Flange/Double Break Bolt-in Bafflequot;: mySfx = 25
gt; gt; gt; Case Is = quot;Angle/Belt/Outward/Weld-inquot;: mySfx = 26
gt; gt; gt; Case Is = quot;Angle/Belt/Outward/Bolt-inquot;: mySfx = 27
gt;
gt; gt; gt; Case Is = quot;Angle/Belt/Inward/Weld-inquot;: mySfx = 28
gt; gt; gt; Case Is = quot;Angle/Belt/Inward/Bolt-inquot;: mySfx = 29
gt; gt; gt; Case Is = quot;Angle/Belt/Inward/Weld-in/Integral Bafflesquot;: mySfx = 30
gt; gt; gt; Case Is = quot;Angle/Belt/Inward/Bolt-in/Integral Bafflesquot;: mySfx = 31
gt; gt; gt; Case Is = quot;Channel/Belt/Outward/Weld-inquot;: mySfx = 32
gt; gt; gt; Case Is = quot;Channel/Belt/Outward/Weld-in/Single Break Bafflequot;:
gt; gt; gt; mySfx = 33
gt; gt; gt; Case Is = quot;Channel/Belt/Outward/Weld-in/Double Break Bafflequot;:
gt; gt; gt; mySfx = 34
gt; gt; gt; Case Is = quot;External Mount Stud Bars/Beltquot;: mySfx = 35
gt; gt; gt; Case Is = quot;Internal Mount Stud Bars/Beltquot;: mySfx = 36
gt; gt; gt; Case Is = quot;Internal Clamp Designquot;: mySfx = 37
gt; gt; gt; End Select
gt; gt; gt; End With
gt; gt; gt;
gt; gt; gt; If mySfx gt; 0 Then
gt; gt; gt; On Error Resume Next
gt; gt; gt; Set myShape = Me.Shapes(quot;shapequot; amp; mySfx)
gt; gt; gt; On Error GoTo 0
gt; gt; gt; If myShape Is Nothing Then
gt; gt; gt; MsgBox quot;error in design--see Garyquot;
gt; gt; gt; Else
gt; gt; gt; myShape.Visible = True
gt; gt; gt; End If
gt; gt; gt; End If
gt; gt; gt;
gt; gt; gt; End Sub
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;
gt;
gt; I made the changes but it still runs through the shapes over and over not
gt; stopping on the name that appears in B44.
gt;
gt; May I send you the entire file so you can see it in action?
gt;
gt;
gt; Cell Mate

And just to add to Gary's post.

He's checking to see what the value is in that cell. He's guessing (and I'd
guess the same) that the value isn't one of those strings you have in your
select case statement.
Gary L Brown wrote:
gt;
gt; Debuging suggest...
gt;
gt; At the end of the Select Case, put a Case Else stmt...
gt;
gt; Case Else
gt; Msgbox quot;Value: quot; amp; .value amp; quot; - quot; amp; mySfx
gt;
gt; Run the macro and see if a message pops up.
gt;
gt; HTH,
gt; --
gt; Gary Brown
gt;
gt; If this post was helpful, please click the ''Yes'' button next to ''Was this
gt; Post Helpfull to you?''.
gt;
gt; quot;Cell Matequot; wrote:
gt;
gt; gt;
gt; gt;
gt; gt; quot;Dave Petersonquot; wrote:
gt; gt;
gt; gt; gt; This line:
gt; gt; gt;
gt; gt; gt; Select Case UCase(.Value)
gt; gt; gt;
gt; gt; gt; means that you have to compare the .value to upper case:
gt; gt; gt; case is = quot;J-FRAME/BELT/BOLT-INquot;
gt; gt; gt; not
gt; gt; gt; case is = quot;J-Frame/Belt/Bolt-inquot;
gt; gt; gt;
gt; gt; gt; I think I'd weasel out of this and do this:
gt; gt; gt;
gt; gt; gt; At the top:
gt; gt; gt;
gt; gt; gt; Option Explicit
gt; gt; gt; Option Compare Text
gt; gt; gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; gt; gt;
gt; gt; gt; then...
gt; gt; gt; Select Case .Value
gt; gt; gt;
gt; gt; gt; Then you'll be doing the same kind of comparison as excel does (ignoring case).
gt; gt; gt;
gt; gt; gt; You can actually keep:
gt; gt; gt; Select Case UCase(.Value)
gt; gt; gt; But it may cause confusion--to you, not excel!
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; Cell Mate wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; I built a code to hide and unhide Bit map shapes. The shapes are quot;stackedquot; on
gt; gt; gt; gt; each other and the code is suppose to turn each one on when it's quot;namequot;
gt; gt; gt; gt; appears in a cell. In this case B44. The quot;namesquot; appear below in quot;quot;. The
gt; gt; gt; gt; problem is that the program just runs through the shapes over and over
gt; gt; gt; gt; instead of stopping on the shape named in the line.
gt; gt; gt; gt;
gt; gt; gt; gt; What am I doing wrong?
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks!
gt; gt; gt; gt; GW
gt; gt; gt; gt;
gt; gt; gt; gt; Option Explicit
gt; gt; gt; gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; gt; gt; gt;
gt; gt; gt; gt; Dim iCtr As Long
gt; gt; gt; gt; Dim mySfx As Long
gt; gt; gt; gt; Dim myShape As Shape
gt; gt; gt; gt;
gt; gt; gt; gt; If Intersect(Target, Range(quot;B44quot;)) Is Nothing Then Exit Sub
gt; gt; gt; gt;
gt; gt; gt; gt; For iCtr = 1 To Me.Shapes.Count
gt; gt; gt; gt; Me.Shapes(quot;shapequot; amp; iCtr).Visible = False
gt; gt; gt; gt; Next iCtr
gt; gt; gt; gt;
gt; gt; gt; gt; With Target
gt; gt; gt; gt; Select Case UCase(.Value)
gt; gt; gt; gt; Case Is = quot;J-Frame/Belt/Bolt-inquot;: mySfx = 1
gt; gt; gt; gt; Case Is = quot;J-Frame/Belt/Weld-inquot;: mySfx = 2
gt; gt; gt; gt; Case Is = quot;J-Frame/Belt/Bolt-in/Integral Bafflesquot;: mySfx = 3
gt; gt; gt; gt; Case Is = quot;J-Frame/Belt/Bolt-in/Integral Baffles/Pillowquot;: mySfx
gt; gt; gt; gt; = 4
gt; gt; gt; gt; Case Is = quot;J-Frame/Belt/Bolt-in/Single Break Bafflequot;: mySfx = 5
gt; gt; gt; gt; Case Is = quot;J-Frame/Belt/Bolt-in/Single Break Baffle/Pillowquot;:
gt; gt; gt; gt; mySfx = 6
gt; gt; gt; gt; Case Is = quot;J-Frame/Belt/Bolt-in/Double Break Bafflequot;: mySfx = 7
gt; gt; gt; gt; Case Is = quot;J-Frame/Belt/Bolt-in/Double Break Baffle/Pillowquot;:
gt; gt; gt; gt; mySfx = 8
gt; gt; gt; gt; Case Is = quot;J-Frame/Belt/Weld-in/Integral Bafflequot;: mySfx = 9
gt; gt; gt; gt; Case Is = quot;J-Frame/Belt/Weld-in/Integral Baffle/Pillowquot;: mySfx =
gt; gt; gt; gt; 10
gt; gt; gt; gt; Case Is = quot;J-Frame/Belt/Weld-in/Single Break Bafflequot;: mySfx = 11
gt; gt; gt; gt; Case Is = quot;J-Frame/Belt/Weld-in/Single Break Baffle/Pillowquot;:
gt; gt; gt; gt; mySfx = 12
gt; gt; gt; gt; Case Is = quot;J-Frame/Belt/Weld-in/Double Break Bafflequot;: mySfx = 13
gt; gt; gt; gt; Case Is = quot;J-Frame/Belt/Weld-in/Double Break Baffle/Pillowquot;:
gt; gt; gt; gt; mySfx = 14
gt; gt; gt; gt; Case Is = quot;Downward Angle/Belt/Inward/Bolt-inquot;: mySfx = 15
gt; gt; gt; gt; Case Is = quot;Downward Angle/Belt/Inward/Weld-inquot;: mySfx = 16
gt; gt; gt; gt; Case Is = quot;Downward Angle/Belt/Inward/Bolt-in/Single Break
gt; gt; gt; gt; Bafflequot;: mySfx = 17
gt; gt; gt; gt; Case Is = quot;Downward Angle/Belt/Inward/Bolt-in/Double Break
gt; gt; gt; gt; Bolt-in Bafflequot;: mySfx = 18
gt; gt; gt; gt; Case Is = quot;Downward Angle/Belt/Inward/Weld-in/Single Break
gt; gt; gt; gt; Bafflequot;: mySfx = 19
gt; gt; gt; gt; Case Is = quot;Downward Angle/Belt/Inward/Weld-in/Double Break
gt; gt; gt; gt; Bafflequot;: mySfx = 20
gt; gt; gt; gt; Case Is = quot;Angle/Flangequot;: mySfx = 21
gt; gt; gt; gt; Case Is = quot;Angle/Flange/Single Break Bafflequot;: mySfx = 22
gt; gt; gt; gt; Case Is = quot;Angle/Flange/Double Break Bafflequot;: mySfx = 23
gt; gt; gt; gt; Case Is = quot;Angle/Flange/Single Break Bolt-in Bafflequot;: mySfx = 24
gt; gt; gt; gt; Case Is = quot;Angle/Flange/Double Break Bolt-in Bafflequot;: mySfx = 25
gt; gt; gt; gt; Case Is = quot;Angle/Belt/Outward/Weld-inquot;: mySfx = 26
gt; gt; gt; gt; Case Is = quot;Angle/Belt/Outward/Bolt-inquot;: mySfx = 27
gt; gt;
gt; gt; gt; gt; Case Is = quot;Angle/Belt/Inward/Weld-inquot;: mySfx = 28
gt; gt; gt; gt; Case Is = quot;Angle/Belt/Inward/Bolt-inquot;: mySfx = 29
gt; gt; gt; gt; Case Is = quot;Angle/Belt/Inward/Weld-in/Integral Bafflesquot;: mySfx = 30
gt; gt; gt; gt; Case Is = quot;Angle/Belt/Inward/Bolt-in/Integral Bafflesquot;: mySfx = 31
gt; gt; gt; gt; Case Is = quot;Channel/Belt/Outward/Weld-inquot;: mySfx = 32
gt; gt; gt; gt; Case Is = quot;Channel/Belt/Outward/Weld-in/Single Break Bafflequot;:
gt; gt; gt; gt; mySfx = 33
gt; gt; gt; gt; Case Is = quot;Channel/Belt/Outward/Weld-in/Double Break Bafflequot;:
gt; gt; gt; gt; mySfx = 34
gt; gt; gt; gt; Case Is = quot;External Mount Stud Bars/Beltquot;: mySfx = 35
gt; gt; gt; gt; Case Is = quot;Internal Mount Stud Bars/Beltquot;: mySfx = 36
gt; gt; gt; gt; Case Is = quot;Internal Clamp Designquot;: mySfx = 37
gt; gt; gt; gt; End Select
gt; gt; gt; gt; End With
gt; gt; gt; gt;
gt; gt; gt; gt; If mySfx gt; 0 Then
gt; gt; gt; gt; On Error Resume Next
gt; gt; gt; gt; Set myShape = Me.Shapes(quot;shapequot; amp; mySfx)
gt; gt; gt; gt; On Error GoTo 0
gt; gt; gt; gt; If myShape Is Nothing Then
gt; gt; gt; gt; MsgBox quot;error in design--see Garyquot;
gt; gt; gt; gt; Else
gt; gt; gt; gt; myShape.Visible = True
gt; gt; gt; gt; End If
gt; gt; gt; gt; End If
gt; gt; gt; gt;
gt; gt; gt; gt; End Sub
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt;
gt; gt; gt; Dave Peterson
gt; gt; gt;
gt; gt;
gt; gt; I made the changes but it still runs through the shapes over and over not
gt; gt; stopping on the name that appears in B44.
gt; gt;
gt; gt; May I send you the entire file so you can see it in action?
gt; gt;
gt; gt;
gt; gt; Cell Mate

--

Dave Peterson