What is VBA Toggle Button?
A toggle button in VBA is a graphical user interface (GUI) element that can be used in user forms or on worksheets. A toggle button has two states: pressed (or active) and unpressed (or inactive). When a user clicks on the toggle button, it toggles between these two states.
Consider the following example:


Create a toggle button by inserting the Toggle button from the ActiveX templates. Then, edit its properties to Linked Cell, where you can see the result of the VBA toggle button state. Here, the cell “H3” shows the toggle state of the button if clicked on.


Table of contents
Key Takeaways
- A VBA Toggle Button is a graphical user interface element that allows users to toggle between two states – pressed and unpressed.
- Toggle Buttons can be inserted on user forms or worksheets in Excel to enhance user interaction.
- You can assign a VBA macro to a Toggle Button, specifying actions to be performed when the button is clicked.
- VBA code associated with a Toggle Button can detect its state using the Value property, distinguishing between pressed (True) and unpressed (False) states.
- Toggle Buttons can be used to trigger actions such as hiding or unhiding rows, columns, or other elements on an Excel worksheet.
How to create VBA Toggle Button?
Follow the steps below to learn how to use the VBA Toggle button properties to your advantage.
Step 1: Once you open the Excel Workbook, in the title bar, choose the “Developer” icon and click on it.

After you click the Developer icon, click on “Insert” and select the “Toggle Button” from the ActiveX controls.

Draw the VBA Toggle Button on any part of the sheet.

Step 2: To view its properties, turn on “Design Mode.” It lets you work on it without triggering the VBA Toggle Button Click Event.

Step 3: After creating a Toggle button, edit its properties.

Step 4: Edit the VBA code of the toggle button to make it perform a function.

Step 5: Clicking “View Code” opens the sheet module with the subroutine already defined.

Step 6: Using an If condition, check the VBA Toggle Button value to see whether it is “True” or “False.”

Step 7: If the VBA Toggle button value is “True,” show a message box stating that the VBA Toggle button state is active.

Step 8: In case the VBA Toggle Button value is “False,” declare an Else condition to print a Message Box stating that the VBA Toggle button state is inactive.

Code:
Private Sub ToggleButton2_Click()
If ToggleButton2.value = True Then
MsgBox “Toggle button is active!”
Else
MsgBox “Toggle button is inactive!”
End If
End Sub
Step 9: Go to the worksheet and click on the Toggle Button.


Now you know how to use the VBA toggle button click event to your advantage. For more interesting scenarios, let us view some interesting examples.
Examples
Given below are some examples of how we can use the toggle button.
Example #1
In this example, you will learn to hide certain columns from the worksheet with a VBA Toggle Button.
Step 1: Create a VBA Toggle button (turn on “Design Mode”).

Step 2: View the properties of the newly made toggle button next to the “Design Mode.”

Edit the properties as needed and change the font.

The toggle button looks something like this.

Step 3: Access the VBA Editor by right-clicking on the toggle button and selecting “View Code.”

Step 4: The name of the subroutine will already be defined, as seen below.

Step 5: Check if the VBA Toggle Button value is “True.” It is done by initializing an If-Else conditional statement.

Step 6: If the button is clicked, hide the column “A” by setting the hidden component as “True.”

Step 7: If the VBA Toggle Button is not “True,” set the Hidden property of column “A” as “False.”

Code:
Private Sub ToggleButton1_Click()
If ToggleButton1.value = True Then
Columns(“A:A”).Hidden = True
Else
Columns(“A:A”).Hidden = False
End If
End Sub
Step 8: Click on the toggle button after turning off the “Design Mode.”



Column “A” is successfully hidden using the VBA toggle button.
Example #2
Here, we learn to hide and unhide a range of rows in an Excel Worksheet.
Step 1: Insert a new toggle button and then turn on “Design Mode” in the “Developer tab” section of the Excel toolbar.

Step 2: View the properties of the newly created toggle button.

Step 3: Edit the properties of the toggle button.

Step 4: Edit the VBA code, declaring the properties of the VBA Toggle Button by right-clicking it and selecting “View Code.”

Step 5: Edit the contents inside the already defined subroutine.

Step 6: Set the Worksheet this toggle button will be working in.

Step 7: Check if the VBA Toggle Button value is “True” using an If-Else condition.

Step 8: If the value is true, set the hidden properties of the rows 2-10 as “False.” It unhides the rows.

Step 9: If the VBA Toggle Button value is “False,” the rows’ hidden property is set as “True.”

Code:
Private Sub ToggleButton1_Click()
If ToggleButton1.value = True Then
Columns(“A:A”).Hidden = True
Else
Columns(“A:A”).Hidden = False
End If
End Sub
Step 10: Click the Toggle in “Sheet1” and see the results.

As seen, rows 2-10 are hidden.

After clicking the button, the rows are seen again.
Example #3
In this example, you can learn how to change the VBA Toggle Button’s color in a UserForm while clicking on it.
Step 1: Once you open the Excel Workbook, in the title bar, choose the “Developer” icon and click on it.

After you click the Developer icon, select “Visual Basic.” This opens the VBA Editor window where you can start coding.

In the VBA Editor, in the title bar, click the “Insert” button and select the “UserForm” option.

Step 2: Edit the UserForm to your preference.

Step 3: Edit the properties of the UserForm to get the result.
The UserForm:

The Frame:

In the Frame, add the Labels, textboxes and option buttons to your preference

Step 4: Create a VBA Toggle button to minimize and maximize the UserForm.

Edit its properties.

Step 5: Double-click on the “Minimize” toggle button to edit its VBA code.

Step 6: Check if the VBA toggle button value is true, that is, it has been clicked.

Step 7: Customize the size of the UserForm to mimic minimizing it.

Step 8: Edit the color of the toggle button.

Using the VBA With, combine similar functions under one for better code readability. The caption is changed to “Maximize”. The back color is then changed to red.
Step 9: If the VBA Toggle Value is “False”, set the original size of the UserForm.

Step 10: Change the caption back to “Minimize” and its back color to “yellow”.

Code:
Private Sub ToggleButton1_Click()
If Me.ToggleButton1.value = True Then
Me.Height = 50
Me.Width = 50
Me.Top = 450
Me.Left = 0
With Me.ToggleButton1
.Left = 0
.Top = 0
.Caption = “Maximize”
.BackColor = vbRed
End With
Else
Me.Height = 340
Me.Width = 330
Me.Top = 150
Me.Left = 400
With Me.ToggleButton1
.Left = 222
.Top = 10
.Caption = “Minimize”
.BackColor = vbYellow
End With
End If
End Sub
Step 11: Run the UserForm and try to minimize the application.

After clicking on the “Minimize” button.

When you click the “Maximize” button.


With this, the back color of the toggle button has been changed with a click.
Important Things To Note
- Design user-friendly interfaces by placing Toggle Buttons logically and ensuring they are easily accessible.
- Keep VBA code modular, separating concerns and promoting reusability for better maintenance.
- Provide visual feedback to users when Toggle Buttons are pressed or unpressed for a more interactive experience.
- Don’t overcrowd your user interface with too many Toggle Buttons; maintain a clean and intuitive design.
- Avoid hardcoding values directly into your Toggle Button code; use variables/constants for flexibility.
Frequently Asked Questions (FAQs)
• Declare a worksheet variable to store the state.
• Save the Toggle Button state in the worksheet variable when the workbook is saved.
• Retrieve the saved state from the worksheet variable when the workbook is reopened.
• Set the Toggle Button state based on the retrieved value.
• Utilize the Workbook Open event in VBA to trigger the state retrieval process automatically.
Yes, you can use the VBA Toggle button with other VBA Controls.
• Combine Toggle Buttons with textboxes, buttons, or other controls to create more interactive forms.
• Place Toggle Buttons alongside checkboxes, combo boxes, and other controls on VBA User Forms.
• Utilize VBA code to implement custom logic and actions triggered by interactions with multiple controls.
• Create dynamic and responsive user interfaces by coordinating actions between Toggle Buttons and other VBA controls.
Yes. It is possible to disable a VBA Toggle button by following the steps below:
• You can disable the VBA Toggle Button using code.
• Access the Toggle Button’s Enabled property in VBA.
• Set ToggleButton1.Enabled = False to disable the button.
• Implement conditions in your code to decide when the Toggle Button should be disabled.
• Visual customization options for VBA Toggle Buttons are somewhat limited compared to more advanced controls.
• Styling options are basic, and extensive formatting may require additional coding.
• Compatibility may vary across Excel versions, so test in the target environment.
• VBA Toggle Buttons have a binary state (pressed or unpressed) and may not support multi-state functionality.
• For dynamic updates, developers may need to manually refresh, or trigger changes based on user actions.
Download Template
This article must be helpful to understand the VBA Toggle Button, with its features and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to VBA Toggle Button. Here we learn how to create and use toggle button in Excel VBA, with step-by-step examples & points to remember. You can learn more from the following articles –
Leave a Reply