Hide/Unhide Shapes Using Drop Down List

[Ancient Wolf]

I searched the forum and could not find any answers that worked for me.

I am trying to find a way to hide and unhide specific shapes when specific clients are selected in a DV controlled drop down list found in cell B3.

I have 10 shapes, currently named simply "bevel 26" and on up to "bevel 36", except there is no "bevel 33".
The clients are Sam's Club, Newcorp, Sanyo, Service Valet, Wal-Mart, Wal-Mart.com, and Wal-Mart New Pilot.

For Sam's Club: All bevels would need to be visible.
For Newcorp: Only bevels 26,30,31,32, and 36 would be visible.
For Sanyo: Only Bevel 26 would show.
For Service Valet: Only Bevel 26 and 35 would be visible.
For the three Wal-Mart clients: Bevels 26,30, and 32 would be the only ones visible.

I have never had any classes on VBA so every time I try to do something new, it is a learning experience for me.

Anyone have any suggestions? Please help.

 

[AlphaFrog]

• Right-click on the sheet tab with the ten shapes.
• Select View Code from the pop-up menu.
• Paste the code below in the VBA edit window.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Address = "$B$3" Then
        Application.ScreenUpdating = False
        With ActiveSheet.Shapes
        
        ' Show all ten bevels
        .Range(Array("bevel26", "bevel27", "bevel28", "bevel29", "bevel30", _
                     "bevel31", "bevel32", "bevel34", "bevel35", "bevel36")).Visible = True
        
        ' Hide specific bevels
        Select Case Target.Value
        
            Case "Sam's Club"   ' hide none
            
            Case "Newcorp"  ' 26,30,31,32, and 36
                .Range(Array("bevel27", "bevel28", "bevel29", _
                             "bevel34", "bevel35")).Visible = False
            
            Case "Sanyo"    ' 26
                .Range(Array("bevel27", "bevel28", "bevel29", "bevel30", _
                             "bevel31", "bevel32", "bevel34", "bevel35", "bevel36")).Visible = False
            
            Case "Service Valet"    ' 26 35
                .Range(Array("bevel27", "bevel28", "bevel29", "bevel30", _
                             "bevel31", "bevel32", "bevel34", "bevel36")).Visible = False
            
            Case "Wal-Mart", "Wal-Mart.com", "Wal-Mart New Pilot"  ' 26 30, 32
                .Range(Array("bevel27", "bevel28", "bevel29", _
                             "bevel31", "bevel34", "bevel35", "bevel36")).Visible = False
            
        End Select
        End With
        Application.ScreenUpdating = True
    End If
    
End Sub

 

[Ancient Wolf]

Thank you for the quick response, AlphaFrog.

I entered your code as instructed, but I am getting a Run Time Error '1004'
The item with the specified name was not found.

The line that is giving me the error is:

.Range(Array("bevel26", "bevel27", "bevel28", "bevel29", "bevel30", _
"bevel31", "bevel32", "bevel34", "bevel35", "bevel36")).Visible = True

 

[AlphaFrog]

I think i missed putting a space in the shape names. You'll have to change...
"bevel26" to "bevel 26"

and...
"bevel27" to "bevel 27"

etc.

 

[Ancient Wolf]

I found the problem AlphaFrog.

I had to edit the names of the bevel shapes in your coding. I remembered that Excel likes to be very specific.

 

[Ancient Wolf]

Thank you again for the help, AlphaFrog. The problem was that I did need to add spacing and capitalize the first letter of bevel. It works great wonderfully now.

Just wondering, though, if I needed all the bevels hidden when no client has been selected, would the case look like this?
Case "" .Range(Array("Bevel 26", "Bevel 27", "Bevel 28", "Bevel 29", "Bevel 30", "Bevel 31", "Bevel 32", "Bevel 34", "Bevel 35", "Bevel 36")).Visible = False

 

[AlphaFrog]

You could use...
Case ""
...to test if the cell is empty. Or...
Case Else
...to test if the cell value is anything else besides the previous cases.

Case Else
    .Range(Array("bevel 26", "bevel 27", "bevel 28", "bevel 29", "bevel 30", _
                 "bevel 31", "bevel 32", "bevel 34", "bevel 35", "bevel 36")).Visible = False

Tip: in your VBA code, if you highlight the word Case in the code and hit the F1 key, you'll get syntax and help on the Case function.

 

[Ancient Wolf]

I went with Case "" since the drop down list in B3 will not change.