Hide control buttons using VBA

**Steffen_DK** · 08-30-2012, 06:53 AM

If you're looking at the excel sheet, what I'm trying to achieve is this:

As default buttons 5-8 should be hidden. When one of the buttons 1-4 is pressed, button 5 should be un-hidden. When another button, 1-4, is pressed, button 6 should be un-hidden an so forth.
When any of the buttons 5-8 are pressed, then the highest-numbered button of those visible, should be hidden.

Say I've pressed all of bottons 1-4, now all buttons are visible. I click on 8, now button 8 gets hidden. I click on 5, now button 7 gets hidden.

I've come along this piece of code, but can't seem to make it work.

ActiveSheet.Shapes("YourShapeName").Visible = False
If anyone can put it in a working sheet, or have a better approach, I'd apriciate it. I guess I'll also need some way to determine how many buttons are visible so I can hide/unhide the correct one, but I think I've got this figured out.

**Andy Pope** · 08-30-2012, 07:06 AM

In a standard code module use

Sub ButtonState()

    Dim shpPressedButton As Shape
    Dim lngIndex As Long
    Dim lngID As Long
    
    Set shpPressedButton = ActiveSheet.Shapes(Application.Caller)
    lngID = CLng(Replace(shpPressedButton.Name, "Button ", ""))
    
    If lngID > 4 Then
        ' hide
        For lngIndex = 8 To 5 Step -1
            If ActiveSheet.Shapes("Button " & lngIndex).Visible Then
                ActiveSheet.Shapes("Button " & lngIndex).Visible = False
                Exit For
            End If
        Next
    Else
        For lngIndex = 5 To 8
            If Not ActiveSheet.Shapes("Button " & lngIndex).Visible Then
                ActiveSheet.Shapes("Button " & lngIndex).Visible = True
                Exit For
            End If
        Next
    End If
End Sub

Assign macro to all buttons.

in Thisworkbook object use

Private Sub Workbook_Open()

    Dim lngIndex As Long
    
    With Worksheets("Ark1")
        For lngIndex = 5 To 8
            .Shapes("Button " & lngIndex).Visible = False
        Next
    End With
End Sub

to hide button 5 thru 8 on openning