See all How-To Articles

How to Insert a Toggle Button in Excel

This tutorial demonstrates how to insert a toggle button in Excel

toggle button intro

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.

vba vbe vbescreen

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.

vba vbe developer

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.

toggle button select activex

 

Click and drag in your spreadsheet your desired size and location in your worksheet, and then release the mouse button to create the button.

toggle button insert 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.

toggle button design mode

 

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.

 

toggle button menu properties

 

The Properties window enables you to customize the appearance and behavior of the toggle button.

toggle button properties

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”

toggle button true 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.

toggle button properties caption

While still in Design mode, right-click on the Toggle button, and click View Code.

toggle button view code

This wil take you into the Visual Basic Editor (VBE) and create the Click Event behind your toggle button.

toggle button vba

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.

 

toggle button 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

toggle button show hide

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.

toggle button hidden columns

 

 

 

 

See all How-To Articles