How To Insert Togggle Button In Excel

In this article we are going to learn how to insert and use a toggle button in 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 enable and disable 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 check box. Either way you can see toggle button are very useful.

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.

Customize The Ribbon

An option dialog box will open. Click on the DEVELOPER check box (it is under Customize the Ribbon Main Tabs) and press Ok.

Add Developer Tab

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.

Insert Controls

Select the Toggle Button in the ActiveX Controls.

Insert Toggle Button

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).

View Button 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

Toggle Code

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.

Hide Row

Click 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

Button Column

Button Cell

That’s it! You have now used the toggle button to hide and unhide multiple rows.

Template

You can download the Template here – Download

RELATED ARTICLESMORE FROM AUTHOR