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

mperrah

Board Regular
Joined
Mar 12, 2005
Messages
62
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
VBA Code:
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
VBA Code:
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
VBA Code:
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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,147
This is a working version to hide a list of images:
VBA Code:
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
Code:
toShow = "222"      'or, probably:  Range("Xn").Value
Worksheets("Campus").Shapes.Range(toShow).Visible = True

Bye
 
Solution

mperrah

Board Regular
Joined
Mar 12, 2005
Messages
62
Code:
toShow = "222"      'or, probably:  Range("Xn").Value
Worksheets("Campus").Shapes.Range(toShow).Visible = True
VBA Code:
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

Board Regular
Joined
Mar 12, 2005
Messages
62
VBA Code:
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

Board Regular
Joined
Mar 12, 2005
Messages
62

ADVERTISEMENT

VBA Code:
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!
 

mperrah

Board Regular
Joined
Mar 12, 2005
Messages
62
Cleaned up and added a loop to be able to scan for changes and not have to hard code the Shape numbers to hide
VBA Code:
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.
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,112
Messages
5,640,160
Members
417,128
Latest member
Xianter

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
Top