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:

vba excel insert activex togglebutton controls sheet

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

vba excel insert togglebutton controls sheet

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

toggle button controls sheet

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:

togglebutton event controls sheet

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"
        'Show the sheet and modify the button text
        Sheets("Sheet2").Visible = -1
        ToggleButton1.Caption = "Sheet 2 is visible"
    End If
End Sub
toggle button pressed controls sheet

The file: togglebutton.xlsm