Excel VBA show Shape based on drop down list using a loop, array, case select?

[mperrah]

I have 2 validations. 1 for Tanks, 1 for Reactors, when we pick the item id like to show the corresponding Shape.
The shapes are named as Numbers.
I need to clear any showing shape of a Tank and only show the chosen validation item,
Same for Reactors.
101 to 131 are Tanks and between 201 to 403 are Reactors

Sub ActivateTank()
 
    Dim mapSht As Worksheet
    Dim myShp As Shape
    Dim myTnk As String
    
    myTnk = "101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 126, 127, 130, 131"

    Set mapSht = Worksheets("Campus")

    With mapSht
         For Each myShp In myTnk
            ActiveSheet.Shapes.Range(Array(" & myTnk & ")).Visible = False
        Next myShp
    End With

End Sub

This is an idea that doesnt work.
myTnk are a list of named shapes that I want to hide or show when a data validation drop down choice is picked.
This code is attempting to hide everything.

The below works but seems clunky and I have another section that has many more entries. wanting to simplify with a loop or something else

Sub Reactors()
' Hide_All Reactors Macro
    With ActiveSheet.Shapes
        .Range(Array("201")).Visible = False
        .Range(Array("202")).Visible = False
        .Range(Array("301")).Visible = False
        .Range(Array("302")).Visible = False
        .Range(Array("303")).Visible = False
        .Range(Array("401")).Visible = False
        .Range(Array("402")).Visible = False
        .Range(Array("403")).Visible = False
        .Range(Array("406")).Visible = False
    End With
    
End Sub

another attempt to hide shapes named a number higher than 200... fail

Sub Reactors2()
' Hide_Reactors Macro
    
    With ActiveSheet.Shapes
    
Dim sObject As Shape
For Each sObject In ActiveSheet.Shapes
If sObject > 200 Then .Visible = False
Next

    End With
End Sub

Any help is appreciated
Mark

 

[Anthony47]

This is a working version to hide a list of images:

Sub HideTanks()
Dim mapSht As Worksheet
Dim myShp As Shape
Dim myTnk As Variant
'
myTnk = Array(101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 126, 127, 130, 131)
'
Set mapSht = Worksheets("Campus")
With mapSht
     For I = 0 To UBound(myTnk)
        mapSht.Shapes.Range(CStr(myTnk(I))).Visible = False
    Next I
End With
End Sub

The array is populated by numbers, much simpler to list than strings.

Making the image visible is easier; use for example

toShow = "222"      'or, probably:  Range("Xn").Value
Worksheets("Campus").Shapes.Range(toShow).Visible = True

Bye

 

[mperrah]

toShow = "222"      'or, probably:  Range("Xn").Value
Worksheets("Campus").Shapes.Range(toShow).Visible = True

Sub Show()

Dim toShow As Variant

toShow = Range("AR5").Value

Worksheets("Campus").Shapes.Range(Array(toShow)).Visible = True

End Sub

something like this? i get error
In my sheet I made a formula find the match in a named range to show the name of the shape I want to show.
Maybe I need to name the shapes the full values of the drop down names?
I was hoping to use just the 3 digit numbers

 

[mperrah]

Sub Show()

Dim toShow As Variant

toShow = Range("AR5").Value

Worksheets("Campus").Shapes.Range(Array(toShow)).Visible = True

End Sub

something like this? i get error
In my sheet I made a formula find the match in a named range to show the name of the shape I want to show.
Maybe I need to name the shapes the full values of the drop down names?
I was hoping to use just the 3 digit numbers

The hide code works perfectly. Thank you!

 

[mperrah]

Sub Show()
Dim toShow As Variant
toShow = Range("AR5").Value
Worksheets("Campus").Shapes.Range(CStr(toShow)).Visible = True

End Sub

Got it to work.
So awesome.
Thank you!

 

[Anthony47]

 

[mperrah]

Cleaned up and added a loop to be able to scan for changes and not have to hard code the Shape numbers to hide

Sub HideTanks()
Dim tBl As Worksheet
Dim cMp As Worksheet
Dim sHp As Shape
Dim tNk As Variant
Dim i As Variant

    Set tBl = Worksheets("Tables")
    Set cMp = Worksheets("Campus")
    
    tNk = tBl.Range("H2:H" & tBl.Range("H" & Rows.Count).End(xlUp).Row)

    With cMp
         For Each i In tNk
            cMp.Shapes.Range(CStr(i)).Visible = False
        Next i
    End With
End Sub

Here is the cleaned up Show code. Its checking the value in a cell with a formula to parse the number from the drop down cell.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim tShow As Variant
Dim rShow As Variant

' unhide shape for tank path
    If Target.Address = "$AC$5" Then
        Call HideTanks
        tShow = Range("AT5").Value
        Worksheets("Campus").Shapes.Range(CStr(tShow)).Visible = True
    End If

' unhide reactor shape path
    If Target.Address = "$AJ$5" Then
        Call HideReactors
        rShow = Range("AR5").Value
        Worksheets("Campus").Shapes.Range(CStr(rShow)).Visible = True
        
    End If
    
End Sub

Thank you Anthony47