In this article, we are going to learn how to insert and use a toggle button in an Excel worksheet. A toggle button is used to indicate a state, such as Yes/No or a mode, such as On/Off. The button alternates between an enabled and disabled state when it is clicked. For example, you can use the toggle button to hide and unhide rows, hide and unhide columns, hide and unhide a chart, switch between design mode and edit mode, or as an alternative to the check box. Either way, you can see that toggle buttons are very useful.
Working with Toggle Buttons
Toggle buttons in Excel are commonly used to create user-friendly interfaces and enhance spreadsheet interactivity. Users can click a toggle button to change its state, which can trigger various actions.
Example Usage
Toggle buttons in Excel find various applications:
| Usage | Functionality |
|---|---|
| Data Filtering | Toggle button to filter data based on specific criteria. |
| View Options | Toggle button to switch between different chart types or data views. |
| Macro Execution | Toggle button to enable/disable a macro’s functionality. |
The ribbon
Follow these steps to insert a Toggle Button in your Excel worksheet.
Note: you can skip steps 1 and 2 if the DEVELOPER tab is already open.
Right click anywhere on the ribbon and select Customize the Ribbon from the drop down menu.
An option dialog box will open. Click on the DEVELOPER check box (it is under Customize the Ribbon Main Tabs) and press Ok.
The developer tap is now visible and is present next to the view tab on the top menu bar. Now click on the DEVELOPER tab and select Insert.
The button
Select the Toggle Button in the ActiveX Controls.
Drag the Toggle button on your Excel worksheet.
Note: you can change the color of the Toggle button, the font, font style, font color, and even the name of the command button by right clicking on it and going to properties (make sure design mode is selected).
Right-click on the Toggle button (make sure the Design mode is selected). From the drop-down menu select View Code. A new window (Visual Basic Editor) will open which will have a dialog box in the center. You will write the code in this dialog box.
Note: you can also double click on your command button (make sure the Design mode is selected) to open a new window (Visual Basic Editor).
The code
Write the following line of code in the dialog box
If ToggleButton1.Value = True Then
'This area contains the things you want to happen
'when the toggle button is not depressed
Rows(4).EntireRow.Hidden = True
Else
'This area contains the things you want to happen
'when the toggle button is depressed
Rows(4).EntireRow.Hidden = False
End If
IMPORTANT: WRITE THIS LINE OF CODE BETWEEN Private Sub and End Sub. OTHERWSE, IT WILL NOT WORK
After writing the above code, you can close the window (Visual Basic Editor) from the upper right. Don’t worry, Excel won’t close.
Click on the toggle button. The result will be that row 4 will get hidden and when you click on the toggle button again, row 4 will be visible to you. That’s it! You have now created and used a toggle button.
Now let’s take it one step further. When the user presses the Toggle Button, multiple rows will hide and unhide. Right-click on the toggle button (make sure design mode is on) and select View Code.
Replace the previous code with this one.
If ToggleButton1.Value = True Then
'This area contains the things you want to happen
'when the toggle button is not depressed
Rows(4).EntireRow.Hidden = True
Rows(5).EntireRow.Hidden = True
Rows(6).EntireRow.Hidden = True
Else
'This area contains the things you want to happen
'when the toggle button is depressed
Rows(4).EntireRow.Hidden = False
Rows(5).EntireRow.Hidden = False
Rows(6).EntireRow.Hidden = False
End If
IMPORTANT: WRITE THIS LINE OF CODE BETWEEN Private Sub and End Sub. OTHERWSE IT WILL NOT WORK
That’s it! You have now used the toggle button to hide and unhide multiple rows. You can use this value in a VBA macro to control your application’s behavior based on the state of the toggle button.
