VBA Code -- Code to Select Shapes Only Working on Certain Shapes

W

wanderingsand

New Member
Joined
Jun 27, 2018
Messages
5
Hello,

I wrote the following code below. Anything referencing shapes 1-15, 101-115 and 201-212 work. Everything else does not and I cannot figure out why. When I run HideSignals or ShowSignal the macro does run, but nothing happens to shapes 21-32. This is true even if I rename them or create new shapes. Any thoughts? I did create a bulk of the document last week and added shapes 21-32 today. Otherwise everything is the same.

Thoughts?

Code: 
Private Sub Worksheet_Calculate()
HideSignals
Select Case [b51]
Case "Protected Intersection (Class IV facilities intersecting on both streets)": Shapes("1").Visible = msoTrue
Case "Protected Intersection (Class IV facilities intersecting a Class II street)": Shapes("2").Visible = msoTrue
Case "Protected Intersection (Class IV facilities intersecting a street with no bicycle facilities)": Shapes("3").Visible = msoTrue
Case "Class IV Bike Lane with a Right-Turn Mixing Zone": Shapes("4").Visible = msoTrue
Case "Class IV Bike Lane with a Right-Turn Mixing Zone (Constrained Version)": Shapes("5").Visible = msoTrue
Case "Class II Bike Lane with a Two-Stage Turn Box": Shapes("6").Visible = msoTrue
Case "Class II Bike Lane with a Conventional Bike Box": Shapes("7").Visible = msoTrue
Case "Class II Bike Lane with No Intersection Treatment": Shapes("8").Visible = msoTrue
Case "Class II Bike Lane with Protected Corners": Shapes("9").Visible = msoTrue
Case "Class IV Bike Lane with a Far-Side Bus Stop": Shapes("10").Visible = msoTrue
Case "Class IV Bike Lane with a Near-Side Bus Stop": Shapes("11").Visible = msoTrue
Case "Class II Bike Lane with a Far-Side Bus Stop": Shapes("12").Visible = msoTrue
Case "Class II Bike Lane with a Near-Side Bus Stop": Shapes("13").Visible = msoTrue
Case "Class II Bike Lane on Left Side of One-Way Street": Shapes("14").Visible = msoTrue
Case "Class IV Bike Lane on Left Side of One-Way Street": Shapes("15").Visible = msoTrue
End Select


Select Case [b53]
Case "Protected Intersection (Class IV facilities intersecting on both streets)": Shapes("101").Visible = msoTrue
Case "Protected Intersection (Class IV facilities intersecting a Class II street)": Shapes("102").Visible = msoTrue
Case "Protected Intersection (Class IV facilities intersecting a street with no bicycle facilities)": Shapes("103").Visible = msoTrue
Case "Class IV Bike Lane with a Right-Turn Mixing Zone": Shapes("104").Visible = msoTrue
Case "Class IV Bike Lane with a Right-Turn Mixing Zone (Constrained Version)": Shapes("105").Visible = msoTrue
Case "Class II Bike Lane with a Two-Stage Turn Box": Shapes("106").Visible = msoTrue
Case "Class II Bike Lane with a Conventional Bike Box": Shapes("107").Visible = msoTrue
Case "Class II Bike Lane with No Intersection Treatment": Shapes("108").Visible = msoTrue
Case "Class II Bike Lane with Protected Corners": Shapes("109").Visible = msoTrue
Case "Class IV Bike Lane with a Far-Side Bus Stop": Shapes("110").Visible = msoTrue
Case "Class IV Bike Lane with a Near-Side Bus Stop": Shapes("111").Visible = msoTrue
Case "Class II Bike Lane with a Far-Side Bus Stop": Shapes("112").Visible = msoTrue
Case "Class II Bike Lane with a Near-Side Bus Stop": Shapes("113").Visible = msoTrue
Case "Class II Bike Lane on Left Side of One-Way Street": Shapes("114").Visible = msoTrue
Case "Class IV Bike Lane on Left Side of One-Way Street": Shapes("115").Visible = msoTrue
End Select


Select Case [j51]
Case "None to Class II Bike Lane (near-side transition)": Shapes("21").Visible = msoTrue
Case "None to Class II Bike Lane (far-side transition)": Shapes("22").Visible = msoTrue
Case "None to Class IV Bike Lane (near-side transition)": Shapes("23").Visible = msoTrue
Case "None to Class IV Bike Lane (far-side transition)": Shapes("24").Visible = msoTrue
Case "Class II Bike Lane to None (near-side transition)": Shapes("25").Visible = msoTrue
Case "Class II Bike Lane to None (far-side transition)": Shapes("26").Visible = msoTrue
Case "Class II Bike Lane to Class IV Bike Lane (near-side transition)": Shapes("27").Visible = msoTrue
Case "Class II Bike Lane to Class IV Bike Lane (far-side transition)": Shapes("28").Visible = msoTrue
Case "Class IV Bike Lane to None (near-side transition)": Shapes("29").Visible = msoTrue
Case "Class IV Bike Lane to None (far-side transition)": Shapes("31").Visible = msoTrue
Case "Class IV Bike Lane to Class II Bike Lane (near-side transition)": Shapes("31").Visible = msoTrue
Case "Class IV Bike Lane to Class II Bike Lane (far-side transition)": Shapes("32").Visible = msoTrue
End Select


Select Case [j53]
Case "None to Class II Bike Lane (near-side transition)": Shapes("201").Visible = msoTrue
Case "None to Class II Bike Lane (far-side transition)": Shapes("202").Visible = msoTrue
Case "None to Class IV Bike Lane (near-side transition)": Shapes("203").Visible = msoTrue
Case "None to Class IV Bike Lane (far-side transition)": Shapes("204").Visible = msoTrue
Case "Class II Bike Lane to None (near-side transition)": Shapes("205").Visible = msoTrue
Case "Class II Bike Lane to None (far-side transition)": Shapes("206").Visible = msoTrue
Case "Class II Bike Lane to Class IV Bike Lane (near-side transition)": Shapes("207").Visible = msoTrue
Case "Class II Bike Lane to Class IV Bike Lane (far-side transition)": Shapes("208").Visible = msoTrue
Case "Class IV Bike Lane to None (near-side transition)": Shapes("209").Visible = msoTrue
Case "Class IV Bike Lane to None (far-side transition)": Shapes("210").Visible = msoTrue
Case "Class IV Bike Lane to Class II Bike Lane (near-side transition)": Shapes("211").Visible = msoTrue
Case "Class IV Bike Lane to Class II Bike Lane (far-side transition)": Shapes("212").Visible = msoTrue
End Select
End Sub


Sub HideSignals()
Shapes("1").Visible = msoFalse
Shapes("2").Visible = msoFalse
Shapes("3").Visible = msoFalse
Shapes("4").Visible = msoFalse
Shapes("5").Visible = msoFalse
Shapes("6").Visible = msoFalse
Shapes("7").Visible = msoFalse
Shapes("8").Visible = msoFalse
Shapes("9").Visible = msoFalse
Shapes("10").Visible = msoFalse
Shapes("11").Visible = msoFalse
Shapes("12").Visible = msoFalse
Shapes("13").Visible = msoFalse
Shapes("14").Visible = msoFalse
Shapes("15").Visible = msoFalse
Shapes("21").Visible = msoTrue
Shapes("22").Visible = msoTrue
Shapes("23").Visible = msoTrue
Shapes("24").Visible = msoTrue
Shapes("25").Visible = msoTrue
Shapes("26").Visible = msoTrue
Shapes("27").Visible = msoTrue
Shapes("28").Visible = msoTrue
Shapes("29").Visible = msoTrue
Shapes("30").Visible = msoTrue
Shapes("31").Visible = msoTrue
Shapes("32").Visible = msoTrue
Shapes("101").Visible = msoFalse
Shapes("102").Visible = msoFalse
Shapes("103").Visible = msoFalse
Shapes("104").Visible = msoFalse
Shapes("105").Visible = msoFalse
Shapes("106").Visible = msoFalse
Shapes("107").Visible = msoFalse
Shapes("108").Visible = msoFalse
Shapes("109").Visible = msoFalse
Shapes("110").Visible = msoFalse
Shapes("111").Visible = msoFalse
Shapes("112").Visible = msoFalse
Shapes("113").Visible = msoFalse
Shapes("114").Visible = msoFalse
Shapes("115").Visible = msoFalse
Shapes("201").Visible = msoFalse
Shapes("202").Visible = msoFalse
Shapes("203").Visible = msoFalse
Shapes("204").Visible = msoFalse
Shapes("205").Visible = msoFalse
Shapes("206").Visible = msoFalse
Shapes("207").Visible = msoFalse
Shapes("208").Visible = msoFalse
Shapes("209").Visible = msoFalse
Shapes("210").Visible = msoFalse
Shapes("211").Visible = msoFalse
Shapes("212").Visible = msoFalse
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = [b51].Address Then
Worksheet_Calculate
End If


If Target.Address = [b53].Address Then
Worksheet_Calculate
End If


If Target.Address = [j51].Address Then
Worksheet_Calculate
End If


If Target.Address = [j53].Address Then
Worksheet_Calculate
End If


End Sub




Sub ShowSignals()
Shapes("1").Visible = msoTrue
Shapes("2").Visible = msoTrue
Shapes("3").Visible = msoTrue
Shapes("4").Visible = msoTrue
Shapes("5").Visible = msoTrue
Shapes("6").Visible = msoTrue
Shapes("7").Visible = msoTrue
Shapes("8").Visible = msoTrue
Shapes("9").Visible = msoTrue
Shapes("10").Visible = msoTrue
Shapes("11").Visible = msoTrue
Shapes("12").Visible = msoTrue
Shapes("13").Visible = msoTrue
Shapes("14").Visible = msoTrue
Shapes("15").Visible = msoTrue
Shapes("21").Visible = msoTrue
Shapes("22").Visible = msoTrue
Shapes("23").Visible = msoTrue
Shapes("24").Visible = msoTrue
Shapes("25").Visible = msoTrue
Shapes("26").Visible = msoTrue
Shapes("27").Visible = msoTrue
Shapes("28").Visible = msoTrue
Shapes("29").Visible = msoTrue
Shapes("30").Visible = msoTrue
Shapes("31").Visible = msoTrue
Shapes("32").Visible = msoTrue
Shapes("101").Visible = msoTrue
Shapes("102").Visible = msoTrue
Shapes("103").Visible = msoTrue
Shapes("104").Visible = msoTrue
Shapes("105").Visible = msoTrue
Shapes("106").Visible = msoTrue
Shapes("107").Visible = msoTrue
Shapes("108").Visible = msoTrue
Shapes("109").Visible = msoTrue
Shapes("110").Visible = msoTrue
Shapes("111").Visible = msoTrue
Shapes("112").Visible = msoTrue
Shapes("113").Visible = msoTrue
Shapes("114").Visible = msoTrue
Shapes("115").Visible = msoTrue
Shapes("201").Visible = msoTrue
Shapes("202").Visible = msoTrue
Shapes("203").Visible = msoTrue
Shapes("204").Visible = msoTrue
Shapes("205").Visible = msoTrue
Shapes("206").Visible = msoTrue
Shapes("207").Visible = msoTrue
Shapes("208").Visible = msoTrue
Shapes("209").Visible = msoTrue
Shapes("210").Visible = msoTrue
Shapes("211").Visible = msoTrue
Shapes("212").Visible = msoTrue
End Sub
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
Hi & welcome to MrExcel.
In what way isn't it working?
You don't seem to be doing anything to those shapes other than making them visible in all three sets of code.
 
Upvote 0
That is actually exactly what I'm doing. All the logic that generates the value in cells B51, B53, J51 and J53 are conditional statements/ formulas (not VBA). So, I thought this would be simple. But the code that works for case B51, B53 and J53 will NOT work to hide/ show shapes for case J51. Additionally, even if I move shapes around to cases B51 or J53 they still will not hide/ show when the macro is run.

Trying to figure out what I'm missing. It's very frustrating because the initial cases were very smooth to code, but this just won't work no matter how I try to modify it (I tried using arrays and only got shapes 21 and 22 to respond.)
 
Upvote 0
Also, thank you for your kind welcome and willingness to help. I've been at this for 3 hours and I'm starting go cross eyed trying to figure out what is different.
 
Upvote 0
That's what I was referring to ;)
But glad you sorted it & thanks for the feedback
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Code to change picture in a spreadsheet not working
Replies
8
Views
578
jrisebo
J
J
  • Question Question
VBA Code not working
Replies
4
Views
2K
jrisebo
J

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top