How to Code Toggle Buttons in VBA

VBA & macros · Updated November 21, 2026

To code a toggle button in Excel VBA, insert an ActiveX Toggle Button, open its worksheet code module, and write a Click event that checks the button’s Boolean Value and responds to its on/off state. For a VBA UserForm, use a ToggleButton control and its Click event instead; the right method depends on where the button will appear.

What a VBA toggle button does

A toggle button keeps one of two states: on or off. Unlike an ordinary command button, which performs an action when clicked and then returns to its unpressed appearance, a toggle button stays pressed while its Value is True and returns to an unpressed appearance when its Value is False. VBA can use that state to change a setting, update a cell, show or hide information, or control another part of a workbook.

Excel has different kinds of controls, and the names can be confusing. An ActiveX Toggle Button is available for a worksheet on Windows. A ToggleButton control can also be placed on a VBA UserForm. A Forms control Button is not a toggle button: it runs a macro when clicked but does not retain an on/off state. A Forms Check Box does retain a checked or unchecked state and is often a suitable alternative.

These controls are not interchangeable. Their insertion steps, event code, and compatibility differ. Decide first whether the control belongs directly on a worksheet or inside a custom dialog, then choose a control that works on every platform where the workbook must be used.

Choose a control for the workbook

  • ActiveX Toggle Button: Use it for an on/off control placed on a Windows worksheet when you need an event procedure and properties such as a caption or linked cell. ActiveX controls are not supported in Excel for Mac.
  • UserForm ToggleButton: Use it inside a VBA UserForm when the toggle is part of a custom dialog or data-entry workflow. The control’s Click event runs when its state changes.
  • Forms Check Box: Choose this for a simple worksheet choice, especially when cross-platform use matters. Its linked cell records TRUE when checked and FALSE when unchecked.
  • Shape assigned to a macro: Consider a shape when you want a custom-looking worksheet control. The macro must manage and store the state; the shape does not provide a built-in toggle Value.

For a workbook shared with Mac users, avoid relying on ActiveX. Excel for the web does not run VBA macros, either. A control may be visible in a workbook without its VBA behavior being available in that environment, so plan for the actual users and Excel versions rather than assuming every control works everywhere.

Insert an ActiveX Toggle Button on a worksheet

In Excel for Microsoft 365 on Windows, show the Developer tab if it is not already visible: choose File > Options > Customize Ribbon, select the Developer check box, and choose OK. Then follow these steps:

  1. Open the worksheet where the control belongs and choose Developer > Insert.
  2. Under ActiveX Controls, select Toggle Button, then drag on the worksheet to draw it.
  3. Keep Design Mode on while editing. Right-click the control and choose Properties to change its name, caption, or other properties.
  4. Right-click the control and choose View Code to open its worksheet code module. Excel creates the control’s Click event procedure.
  5. Enter the event code, return to the worksheet, turn off Design Mode, and click the control to check its behavior.

The Properties window’s (Name) property is the identifier used in VBA; Caption is the text displayed on the control. For example, a control named ToggleButton1 can have the caption “Off” initially. Choose names that make the control’s purpose clear, particularly if the sheet contains several buttons. To open the Visual Basic Editor directly, use Alt+F11 on Windows. On a Mac, the VBA editor shortcut can depend on the keyboard and function-key settings; ActiveX worksheet controls themselves are not available there.

Write the worksheet Click event

This example assumes the ActiveX control is named ToggleButton1 and is on the worksheet whose code module contains the event. It writes the current state to cell B2 and changes the caption. Do not set a LinkedCell property for this example, because the code writes the state itself.

Private Sub ToggleButton1_Click()
    Dim isOn As Boolean

    ' Read the state after the click.
    isOn = CBool(Me.ToggleButton1.Value)

    If isOn Then
        Me.ToggleButton1.Caption = "On"
        Me.Range("B2").Value = "On"
    Else
        Me.ToggleButton1.Caption = "Off"
        Me.Range("B2").Value = "Off"
    End If
End Sub

Because this is an ActiveX event procedure, put it in the worksheet’s code module, not in a standard module. In the editor, the event is normally created by selecting the control and its Click event from the code window’s drop-down lists. The Me keyword refers to the worksheet object that owns the procedure, so both the control and B2 are referenced on that sheet.

To use another output location, replace B2 with the intended cell address. If the button should change a real workbook setting, replace the two cell-writing statements with the relevant action. Keep the action narrow and predictable: a toggle can be clicked repeatedly, so its event should safely handle both turning on and turning off.

Use a linked cell instead of writing the state manually

An ActiveX Toggle Button can be connected to a worksheet cell through its LinkedCell property. Select the control while Design Mode is on, open Properties, and enter a cell reference such as $B$2 for LinkedCell. Excel then reflects the control’s Boolean state in that cell: a selected toggle corresponds to TRUE and an unselected toggle to FALSE.

Linked cells are useful when formulas or other worksheet features need to read the state. For example, a formula can test B2, or conditional formatting can use the cell’s value. If you use a linked cell, avoid also writing a different text value such as “On” or “Off” to that same cell in the Click event; the control expects the linked cell to represent its Boolean state. Put a human-readable status in a separate cell if needed.

Use a Forms Check Box when a worksheet only needs a simple checked/unchecked input. Select Developer > Insert > Check Box under Form Controls, draw it, and use its context menu to format the control and set a cell link. The linked cell provides TRUE or FALSE without needing an event procedure. The exact context-menu wording can vary with the control and Excel version, but the cell link is set in the control’s formatting options.

Code a ToggleButton on a UserForm

To make a dialog, open the Visual Basic Editor, insert a UserForm, and add a ToggleButton from the Toolbox. Set its (Name) property to ToggleButton1 and its Caption property to Off. Double-click the control to create its Click event, then use code such as this in the UserForm’s code module:

Private Sub ToggleButton1_Click()
    Dim isOn As Boolean

    ' The Value changes when the user clicks the control.
    isOn = CBool(Me.ToggleButton1.Value)

    If isOn Then
        Me.ToggleButton1.Caption = "Enabled"
        Me.Label1.Caption = "The option is on."
    Else
        Me.ToggleButton1.Caption = "Disabled"
        Me.Label1.Caption = "The option is off."
    End If
End Sub

This example assumes the UserForm also contains a Label named Label1. If it does not, add a label with that name or remove the two Label1 lines. To set the initial state deliberately, add a UserForm_Initialize event in the same code module:

Private Sub UserForm_Initialize()
    ' Start the option in the off position.
    Me.ToggleButton1.Value = False
    Me.ToggleButton1.Caption = "Disabled"
End Sub

Initialization matters because a form should not depend on whatever state a control happened to retain from a previous display. Set the initial value and its caption together so the visible label agrees with the actual Boolean state. A UserForm control is separate from an ActiveX control placed directly on a worksheet; its event code belongs to the form module.

Common problems and safe testing

If clicking the worksheet control does nothing, first check that Design Mode is off. When Design Mode is on, Excel lets you select and edit an ActiveX control rather than use it normally. Also confirm that the control’s (Name) property matches the event procedure name, that the procedure is in the correct worksheet module, and that macros are permitted by the workbook’s security settings.

  • The code will not compile: Check that control names and worksheet references are spelled correctly and that paired If and End If statements are present.
  • The displayed caption disagrees with the state: Set Value and Caption together during initialization, and update the caption in both branches of the Click event.
  • The linked cell seems to change unexpectedly: Check that the cell is not also being overwritten by event code, formulas, or another macro.
  • The control is unavailable or inactive: Confirm that the workbook is open in desktop Excel on Windows for an ActiveX control, not Excel for Mac or Excel for the web.
  • The file loses its macro code: Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm), not as a macro-free .xlsx file.

Before running a macro, save a copy of the workbook. VBA macro actions cannot be undone with Ctrl+Z, so test on a copy and verify both the on and off paths. On Windows, Ctrl+S saves the workbook; on Mac, use Command+S. Use trusted macro files and do not enable macros in an unfamiliar workbook simply to make a button work.

Alternatives and platform limits

For Mac users, a Forms Check Box with a linked cell is often the straightforward worksheet alternative to an ActiveX Toggle Button. A shape assigned to a macro can also provide a custom appearance, but its code needs a reliable place to store the current state, such as a dedicated cell or a workbook-level variable. A cell-based state is usually easier to inspect and maintain than a hidden variable, although the sheet should make clear which cell is used.

For Excel for the web, use worksheet features that do not require VBA if the workbook’s users must interact with it in a browser. VBA code can be stored in a macro-enabled workbook, but the browser does not execute it. When a workbook will move between Windows, Mac, and web versions, test the chosen alternative in each environment and explain any desktop-only steps to users.

Frequently Asked Questions

How do I check whether a VBA toggle button is on? Read its Value property. For an ActiveX control named ToggleButton1, the condition is based on ToggleButton1.Value; True means on and False means off. In an event procedure on its worksheet, Me.ToggleButton1.Value identifies that control.

Can I use a Forms button as a toggle button? Not as a built-in toggle. A Forms Button runs an assigned macro but does not retain an on/off Value. Use a Forms Check Box for a native checked state, or create a macro-driven shape and store its state separately.

Why does my ActiveX toggle button not work on a Mac? Excel for Mac does not support ActiveX controls. Replace the worksheet control with a Forms Check Box or another cross-platform approach, and test any VBA-dependent behavior on the intended Mac version. Excel for the web does not run VBA macros.

Where should I put the Click event code? Put an ActiveX worksheet button’s Click event in that worksheet’s code module. Put a UserForm ToggleButton’s Click event in the UserForm’s code module. A regular macro assigned to a shape belongs in a standard module.

Do toggle button macros work with Ctrl+Z? No. Excel’s Undo history does not reverse changes made by a VBA macro. Save a backup copy before running code that changes workbook data, and test the macro on a copy first.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.