How To Insert Togggle Button In Excel
Follow these steps to insert an Toggle Button in your Excel worksheet
Note: you can skip step 1 and step 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.
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 the new window (Visual Basic Editor).
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. Now when the user press 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.
