VBA Toggle Button

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:

VBA Toggle - Intro - Togglebutton1
VBA Toggle - Intro - properties

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.

VBA Toggle - Intro - True
VBA Toggle - Intro - False
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.

VBA Toggle - create - Step 1

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

VBA Toggle - create - Step 1 - insert - toggle

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

VBA Toggle - create - Step 1 - draw toggle

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

VBA Toggle - create - Step 2

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

VBA Toggle - create - Step 3

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

VBA Toggle - create - Step 4

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

VBA Toggle - create - Step 5

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

VBA Toggle - create - Step 6

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

VBA Toggle - create - Step 7

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.

VBA Toggle - create - Step 8

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.

VBA Toggle - create - Step 9 - Active
VBA Toggle - create - Step 9 - inactive

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”).

VBA Toggle - create - Step 1 - insert - toggle

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

VBA Toggle - Example 1 - Step 2

Edit the properties as needed and change the font.

VBA Toggle - Example 1 - Step 2 - properties

The toggle button looks something like this.

VBA Toggle - Example 1 - Step 2 - hide

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

VBA Toggle - create - Step 3

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

VBA Toggle - create - Step 4

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

VBA Toggle - create - Step 5

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

VBA Toggle - Example 1 - Step 6

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

VBA Toggle - Example 1 - Step 7

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.”

VBA Toggle - Example 1 - Step 8
VBA Toggle - Example 1 - Step 8 - hide column A
VBA Toggle - Example 1 - Step 8 - hidden

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.

VBA Toggle - Example 2 - Step 1

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

VBA Toggle - Example 2 - Step 2

Step 3: Edit the properties of the toggle button.

VBA Toggle - Example 2 - Step 3

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

VBA Toggle - Example 2 - Step 4

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

VBA Toggle - Example 2 - Step 5

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

VBA Toggle - Example 2 - Step 6

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

VBA Toggle - Example 2 - Step 7

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

VBA Toggle - Example 2 - Step 8

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

VBA Toggle - Example 2 - Step 9

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.

VBA Toggle - Example 2 - Step 10

As seen, rows 2-10 are hidden.

VBA Toggle - Example 2 - Step 10 - 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.

Step 1

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

Example 3 - Step 1 - Developer

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

Example 3 - Step 1 - Module

Step 2: Edit the UserForm to your preference.

Example 3 - Step 2

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

The UserForm:

Example 3 - Step 3

The Frame:

Example 3 - Step 3 - Frame

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

Example 3 - Step 3 - Labels

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

Example 3 - Step 4 - Minimize

Edit its properties.

Example 3 - Step 4 - properties

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

Example 3 - Step 5

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

Example 3 - Step 6

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

Example 3 - Step 7

Step 8: Edit the color of the toggle button.

Example 3 - Step 8

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.

Example 3 - Step 9

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

Example 3 - Step 10

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.

Example 3 - Step 11

After clicking on the “Minimize” button.

Example 3 - Step 11 - minimize

When you click the “Maximize” button.

Example 3 - Step 11 - maximize
Example 3 - Step 11 - mini

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)

1. How do I ensure the toggle button state persists when I reopen the Excel file?

• 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.

2. Can I use a VBA toggle button in conjunction with other VBA controls?

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.

3. Is it possible to disable a VBA toggle button under certain conditions?

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.

4. Are there any limitations or considerations when using VBA toggle buttons?

• 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.

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 –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *