How to create a  toggle button in Microsoft Excel

In this tutorial, we will learn how to create a toggle button in Microsoft Excel. A toggle button is a commonly used feature in Excel that allows users to switch between two states by clicking on the button. To create a toggle button we can use the Developer tab in the menu bar and then insert the toggle button using the Insert options. The toggle button can then be assigned a VBA code to perform a specific action when clicked.

A toggle button is a graphical user interface element that allows users to switch between two states, typically on/off or active/inactive. It is often represented by a button that changes its appearance when clicked, indicating whether the feature or setting it controls is turned on or off.

Step 1 – Enable the Developer Tab

– Enable the Developer tab by going into the File tab.
– Click on the Option and go to the Customize Ribbon options.
– Enable the Developer tab by checking the box prior to the Developer tab option.

Step 2 – Go to the Developer Tab

– Go to the Developer tab in the menu bar.

Step 3 – Click on the Insert Button

– Click on the Insert button.
– A drop-down menu will appear.

Step 4 – Create a Toggle Button

– Click on the Toggle button option and place it by clicking anywhere on the sheet.

Step 5 – Right-Click on the Toggle Button and Click on the Properties

– Right-click on the Toggle button.
– Click on the Properties option in the context menu.

Step 6 – Edit the Caption of the Toggle Button

– Edit the caption of the toggle button.

Step 7 – Right-Click on the Toggle Button and Click on the View Code Option

– Right-click on the toggle button and click on the View Code option.


Step 8 – Enter the VBA Code to Make the Toggle Button Functional

– Enter the VBA code in the editor according to the function.
– Here we have entered the code for Hiding Columns.

Private Sub ToggleButton1_Click()
    If ToggleButton1.Value = True Then
        ‘If the toggle button is in the “on” state, hide columns C to E
        Columns(“C:F”).EntireColumn.Hidden = True
    Else
        ‘If the toggle button is in the “off” state, unhide columns C to E
        Columns(“C:F”).EntireColumn.Hidden = False
    End If
End Sub

– The range of columns to be hidden is specified as “C: F”.
– Run the VBA code by pressing F5 or the Run Sub button as shown above. This will hide the columns from C to F.

Step 9 – Click on the Toggle Button

– Close the VBA editor. 
– Click on the toggle button i.e. “Hide Columns”.
– The specified columns would be hidden or made visible depending upon the last state as shown above.