VBA & Macro Problems

How to Create a Global VBA Procedure for Multiple Excel Toggle Buttons

Jason Miller Oct 20, 2026 870 views

Question details

The user needs a method to handle click events for multiple Excel ActiveX toggle buttons using one shared VBA procedure, rather than creating separate event routines for each button.

How to Create a Global VBA Procedure for Multiple Excel Toggle Buttons

Product: Excel
Device & OS: not provided
Scenario: Optimizing VBA code in a worksheet that contains multiple ActiveX toggle buttons which all require the same caption or color updates upon being clicked.
Observed behavior: Instead of duplicating code by assigning individual click events to each toggle button, the goal state is to intercept events from all toggle buttons dynamically and process them in a single, globally defined VBA class module.

Before you start

Ensure the Developer tab is enabled in your ribbon and that your workbook is saved as a Macro-Enabled Workbook (.xlsm) to allow VBA code execution.

Solution 1Recommended

Use a VBA Class Module with WithEvents

By defining a custom Class Module with a WithEvents variable, you can create a shared event handler. A collection in ThisWorkbook will keep these object instances active.

To make multiple ActiveX controls share a single event procedure, you must wrap the control within a custom Class Module. The WithEvents keyword allows the custom class to listen to the events of the MSForms.ToggleButton. By initializing these class instances when the workbook opens and storing them in a global collection, the shared click event remains active during your session.

Insert a Custom Class Module

Press Alt + F11 to open the VBA Editor. In the Project Explorer, right-click your workbook, select Insert, and choose Class Module. In the Properties window, rename the module to MyToggleButton.

Declare the WithEvents Variable

Inside the MyToggleButton class module, write the following declaration: Public WithEvents MTB As MSForms.ToggleButton.

Create the Shared Click Event

Still in the class module, create the event handler: Private Sub MTB_Click(). Inside this sub, write your logic to update the caption or background color based on the MTB.Value. Use valid RGB functions for colors, such as MTB.BackColor = RGB(255, 0, 0).

Set Up the Collection in ThisWorkbook

Double-click ThisWorkbook in the Project Explorer. At the top of the module, declare a global collection to hold the button instances: Dim ToggleCollection As New Collection.

Initialize Buttons on Workbook Open

Create a Private Sub Workbook_Open() in the ThisWorkbook module. Write a loop to iterate through each Worksheet and each OLEObject. If the object's ProgID is Forms.ToggleButton.1, create a new instance of MyToggleButton, assign the object to the MTB property, and add the instance to your ToggleCollection.

Use RGB for Color Formatting: When dynamically changing the BackColor property via VBA, always use valid VBA color constants (like vbRed) or RGB values (e.g., RGB(128, 128, 128)) rather than hexadecimal literals to avoid type mismatch errors.

Advanced Macro Automation

Write and Execute VBA Macros Easily in WPS Spreadsheet

WPS Spreadsheet fully supports VBA macros, allowing you to create custom class modules and automate repetitive tasks across your workbooks. You can easily manage ActiveX controls and global procedures without modifying your existing Excel VBA code.

1. Enable Developer Tools: Launch WPS Spreadsheet, navigate to the Developer tab on the top ribbon, and click on the VBA Editor icon to access your project files.
2. Insert Modules and Classes: Right-click on your VBAProject tree, select Insert, and choose Class Module to build your global event handlers exactly as you would in Microsoft Excel.
3. Save and Run Macros: Save your document as an .xlsm file to preserve your VBA code, then test your global toggle button procedures effortlessly within the workspace.

Fully compatible with Microsoft Excel VBA syntax, OLEObjects, and UserFormsBuilt-in Developer tools for advanced macro editing and debuggingLightweight application with high performance for executing complex loops

microsoft office alternative - wps office

Frequently Asked Questions

Why are my global toggle button click events not triggering?

This usually happens because the custom class instances are not stored in a global or module-level Collection. If the Collection is declared locally within the Workbook_Open event, it gets destroyed once the sub finishes running, causing the event handlers to disconnect.

How do I identify ActiveX toggle buttons among other shapes or objects in a VBA loop?

You can identify ActiveX toggle buttons by checking the ProgID property of each OLEObject in your worksheet. For a standard ActiveX toggle button, the ProgID evaluates exactly to 'Forms.ToggleButton.1'.

Can I use hexadecimal color codes for the toggle button background color?

Using hexadecimal string literals directly for the BackColor property can lead to errors. It is strongly recommended to use the built-in VBA RGB function, such as RGB(255, 0, 0) for red, or standard VBA color constants.

Excel & Data Problems