How to Insert a Toggle Button in Excel
This tutorial demonstrates how to insert a toggle button in Excel
Excel allows you to insert a toggle button into your spreadsheet which can run a macro that you have created and stored in that spreadsheet. A toggle button is literally an on/off button – when you click it, it will depress in, and when you click it again, it would pop back out. To insert a toggle button, you need to have the Developer Ribbon in Excel visible.
Developer Ribbon
The Developer Ribbon contains a group called controls which are needed to insert a toggle button. It also contains everything to do with macros. A macro is needed to create the functionality behind the toggle button.
If you don’t see the Developer Ribbon, you will need to enable it.
Enable the Developer Ribbon
Click on the File tab in the Ribbon, and go down to Options. In the Customize Ribbon options, tick the Developer check box. This is switched off by default so you will need to switch it on to see the tab on the ribbon.
Click OK.
The Developer tab will appear on the main Ribbon.
Inserting a Toggle Button
In the Ribbon, select Developer > Controls > Insert > ActiveX Controls and then select the toggle button control.
Click and drag in your spreadsheet your desired size and location in your worksheet, and then release the mouse button to create the button.
Your toggle button will have ‘handles’ around it which enable you to re-size it to your requirement, and also indicates that the toggle button is in Design Mode.
In the Developer Ribbon, you will notice that the Design Mode button is depressed. This button is actually a toggle button itself. If you click on it, you will go out of Design mode and the ‘handles’ will disappear from around the toggle button.
However, while we are still in Design Mode and with the Toggle button is selected, lets look at the Properties of the Toggle button.
In the Ribbon, select Developer > Controls > Properties.
The Properties window enables you to customize the appearance and behavior of the toggle button.
Some Common properties that can be adjusted are:
Name: you can type a more appropriate name for the toggle button if required such as btnToggleColumns.
Caption: you can amend the caption for the toggle. If for example, the toggle button was going to show and hide columns, you might like to change the caption to Hide Columns. (see below to change it to Show Columns when the columns are hidden).
ForeColor: you can amend the fore color of the button from the default grey to whichever colour you require.
Height & Width: these indicate the size of your button and you can amend them in the properties as well as dragging the toggle button handles.
Text Align: by default the caption is in the center of the button, but you can change this to the left or right of the button.
Linked Cell: you can link the button to a cell. If you do this, when you depress the button, the linked cell will be populated with the word “TRUE”. When you click he button again, the cell will change to “FALSE”
Amending the Caption of the Toggle Button
There are 2 ways of amending hte caption of the Toggle button – with the Caption property and in VBA Code.
In the Properties dialog box, type in a new caption for the toggle button.
While still in Design mode, right-click on the Toggle button, and click View Code.
This wil take you into the Visual Basic Editor (VBE) and create the Click Event behind your toggle button.
Type in the following code:
Private Sub btnToggleColumns_Click()
If btnToggleColumns = True Then
btnToggleColumns.Caption = "Show Columns"
Else
btnToggleColumns.Caption = "Hide Columns"
End If
End Sub
Close the VBE to return to the spreadsheet and then, in the Ribbon, select Developer > Controls > Design Mode to switch off Design Mode.
Click on the toggle button to amend the caption to “Show Columns“. Click again on the toggle button to switch it to “Hide Columns”
Adding Functionality to the Toggle Button
To add the required functionality to the toggle button, we need to go back to our VBA code.
In the Ribbon, select Developer > Controls > Design Mode to switch Design Mode back on again.
Once again, right-click on the Toggle button, and click View Code.
Amend your click Event to include code to show and hide the columns.
Private Sub btnToggleColumns_Click()
If btnToggleColumns = True Then
btnToggleColumns.Caption = "Show Columns"
Columns("D:E").EntireColumn.Hidden = True
Else
btnToggleColumns.Caption = "Hide Columns"
Columns("D:E").EntireColumn.Hidden = False
End If
End Sub
Close the VBE and return to Excel. Now when you click the toggle button, the caption should change and columns D and E should be either hidden or shown depending on the value (true or false) of the button.
