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