| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA Code
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA Code
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 wrote: 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA Code
"Dave Peterson" wrote: 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 wrote: 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 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA Code
|
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA Code
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Change case...help please | Excel Worksheet Functions | |||
| Macro for changing text to Proper Case | Excel Worksheet Functions | |||
| Using other workbooks.. | Excel Worksheet Functions | |||
| Make Change Case in Excel a format rather than formula | Excel Worksheet Functions | |||
| Opening a file with code without a set file name | Excel Discussion (Misc queries) | |||
Linear Mode
