VBA Code

[Cell Mate]

I built a code to hide and unhide Bit map shapes. The shapes are "stacked" on
each other and the code is suppose to turn each one on when it's "name"
appears in a cell. In this case B44. The "names" appear below in "". 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("B44")) Is Nothing Then Exit Sub

For iCtr = 1 To Me.Shapes.Count
Me.Shapes("shape" & iCtr).Visible = False
Next iCtr

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

If mySfx > 0 Then
On Error Resume Next
Set myShape = Me.Shapes("shape" & mySfx)
On Error GoTo 0
If myShape Is Nothing Then
MsgBox "error in design--see Gary"
Else
myShape.Visible = True
End If
End If

End Sub

 

[Dave Peterson]

This line:

Select Case UCase(.Value)

means that you have to compare the .value to upper case:
case is = "J-FRAME/BELT/BOLT-IN"
not
case is = "J-Frame/Belt/Bolt-in"

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]

This line:

Select Case UCase(.Value)

means that you have to compare the .value to upper case:
case is = "J-FRAME/BELT/BOLT-IN"
not
case is = "J-Frame/Belt/Bolt-in"

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!

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

 

[Gary L Brown]

Debuging suggest...

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

Case Else
Msgbox "Value: " & .value & " - " & mySfx

Run the macro and see if a message pops up.

HTH,

 

[Dave Peterson]

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.