VBA Course: Controls (Part 3)

Insertion on a Worksheet

Controls can also be used on an Excel worksheet. For this example, we will add the control directly on the sheet.

Toggle Button (ToggleButton)

Start by inserting a toggle button (ActiveX control) from the Developer tab:

Note that to manipulate an ActiveX control on a sheet, Design Mode must be enabled:

Now double-click on the button and modify its properties to achieve the following:

The goal here is to hide Sheet 2 when the button is pressed or show it otherwise.

The previous double-click also added the Click event of the button on the sheet where the button is located:

Now you just need to enter the instructions to be executed when the button is clicked:

Private Sub ToggleButton1_Click()
    
    'If the button is pressed
    If ToggleButton1 Then
        
        'Hide the sheet and modify the button text
        Sheets("Sheet2").Visible = 2
        ToggleButton1.Caption = "Sheet 2 is hidden"
        
    'Otherwise
    Else
    
        'Show the sheet and modify the button text
        Sheets("Sheet2").Visible = -1
        ToggleButton1.Caption = "Sheet 2 is visible"
        
    End If
    
End Sub

The file: togglebutton.xlsm