How to create a toggle button in Microsoft Excel
By
SpreadCheaters
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.
