How to Code Toggle Buttons in VBA

VBA & macros · Updated November 11, 2026

To code a toggle button in Excel VBA, insert an ActiveX Toggle Button, open its worksheet code window, and use its Click event to check whether its value is on or off. The code below changes the button caption and color and writes the state to a cell. This guide is for Excel for Microsoft 365 on Windows; ActiveX controls are not supported in Excel for Mac or Excel for the web.

What You’ll Need

  • Excel for Microsoft 365 on Windows, with a workbook saved as an Excel Macro-Enabled Workbook (.xlsm).
  • The Developer tab enabled. If it is missing, go to File > Options > Customize Ribbon, select Developer in the right-hand list, and choose OK.
  • Permission to run macros. Excel may ask you to enable content when you open the workbook. Only enable macros in files you trust.
  • A worksheet cell to display the state. The example uses D2; choose another cell if that is more suitable for your sheet.

Step-by-Step

  1. Save a copy before changing or running VBA. Make a backup of the workbook first. Macro actions cannot be undone with Ctrl+Z, so a separate copy gives you a way back if the control or code behaves unexpectedly. Save the working file as an Excel Macro-Enabled Workbook (.xlsm), not as a standard .xlsx file, which does not retain VBA code.
  2. Insert an ActiveX Toggle Button. Open the worksheet where you want the control. Select Developer > Insert, then under ActiveX Controls choose Toggle Button. Drag on the sheet to draw it. If Excel is not in Design Mode, select Developer > Design Mode before editing or repositioning controls. A Toggle Button stays in its on or off state after a click, unlike a standard button that simply runs a macro.
  3. Set the control’s name and starting caption. With Design Mode on, right-click the new button and choose Properties. In the Properties window, set (Name) to ToggleButton1 and Caption to OFF. The name is what VBA uses to refer to the control; the caption is the text readers see. If you choose a different name, use that exact name in the code rather than ToggleButton1.
  4. Open the button’s Click event. Right-click the control while Design Mode is on and choose View Code. Excel opens the Visual Basic Editor and usually creates a procedure for the button’s Click event. Put the code in the worksheet’s code module, not in a standard module such as Module1. A worksheet ActiveX control’s event procedure belongs to the sheet that contains that control. On Windows, Alt+F11 opens or switches to the Visual Basic Editor; on Mac, Option+F11 may require Fn as well, but the ActiveX control steps themselves are Windows-only.
  5. Add code for both states. Replace the generated event procedure, or fill in its empty body, with the following. Confirm the procedure name matches the control name and that the worksheet cell you want to update is D2.
Private Sub ToggleButton1_Click()
    Dim isOn As Boolean

    ' Read the button's current on/off value.
    isOn = Me.ToggleButton1.Value

    If isOn Then
        ' Show the on state and record it in the worksheet.
        Me.ToggleButton1.Caption = "ON"
        Me.ToggleButton1.BackColor = RGB(0, 176, 80)
        Me.Range("D2").Value = "Enabled"
    Else
        ' Show the off state and record it in the worksheet.
        Me.ToggleButton1.Caption = "OFF"
        Me.ToggleButton1.BackColor = RGB(192, 0, 0)
        Me.Range("D2").Value = "Disabled"
    End If
End Sub
  1. Understand what the code changes. When clicked, the Toggle Button’s Value is either True or False. The code stores that value in the declared Boolean variable isOn, then uses an If statement to choose the matching caption, background color, and text in D2. Me refers to the worksheet containing the control and its code, which helps prevent the code from accidentally writing to a different active sheet.
  2. Return to Excel and test the button. Close or switch away from the Visual Basic Editor. On the Developer tab, turn off Design Mode; while Design Mode is on, clicking a control selects or edits it rather than using it normally. Click the button once and check that it shows ON, turns green, and puts Enabled in D2. Click again and confirm it shows OFF, turns red, and puts Disabled in D2.
  3. Save, close, and reopen to check the workbook. Save the .xlsm file, close it, and reopen it. If Excel displays a security warning, enable macros only if you trust the workbook and its source. Test the button again. This checks that the code has been saved and that the workbook opens with the expected macro behavior.

If your worksheet uses a different cell, change both occurrences of Me.Range("D2") to the cell you chose. You can also replace Enabled and Disabled with more meaningful labels, such as Filter on and Filter off. Keep the quotes around text values and make sure the range reference is valid on the worksheet that holds the control.

Common Mistakes

Putting the event code in the wrong place: A worksheet ActiveX button’s Click event needs to be in that worksheet’s code module. Code in a standard module will not automatically respond to the button click. In the Visual Basic Editor’s Project Explorer, find the workbook, expand Microsoft Excel Objects, and open the sheet containing the button.

Leaving Design Mode on: In Design Mode, clicking the control does not run its Click event. Turn off Developer > Design Mode before testing. Conversely, turn Design Mode on to select, resize, or edit the button.

Using a mismatched control name: If the control’s Properties window says its name is ToggleButton2, a procedure for ToggleButton1 will not handle that control. Use the same name in the procedure and every reference to the button. Do not confuse the (Name) property with the visible Caption.

Saving in the wrong format: Saving as .xlsx removes VBA code. Keep the workbook in .xlsm format. Also remember that macro security settings may prevent code from running until the file is trusted or its macros are enabled.

Expecting Ctrl+Z to reverse a macro: Excel clears its undo history when VBA runs. The code here writes a value to D2, and that action cannot be reversed with Ctrl+Z. Save a backup before testing changes, especially if you expand the macro to modify more cells.

Tips

Choose captions that make the current state obvious. ON and OFF are concise, but labels such as Show details and Hide details can be clearer if the button controls a specific feature. Keep the cell status text equally explicit so formulas or other VBA procedures can use it without relying on the button’s color.

Do not use color as the only signal. Some readers cannot distinguish red and green easily, and colors may be displayed differently with accessibility settings. The text caption and the value in D2 provide additional ways to identify the state. If the sheet is protected, test whether the code can update D2 and whether users can interact with the control under that protection setup.

For a larger project, put the state-changing work in a separate procedure so more than one control can call it. Keep event procedures short and use clear names for controls and cells. If other code changes the button’s Value or the status cell, also plan how to keep the caption, color, and recorded state synchronized; this example updates them when the user clicks the button.

ActiveX Toggle Buttons are not portable across Excel platforms. Excel for Mac and Excel for the web do not support this worksheet ActiveX approach, and Excel for the web cannot run VBA macros. If people using those versions need an on/off input, consider a supported alternative such as a cell-based choice or a form control, and design a separate interaction for that platform rather than assuming this control will work everywhere.

Troubleshooting

The button does nothing: First turn off Design Mode. Then check that macros are enabled for this trusted workbook, the file is saved as .xlsm, and the code is in the correct worksheet module. In the Visual Basic Editor, make sure the procedure is named exactly ToggleButton1_Click when the control’s name is ToggleButton1.

The button changes state but D2 does not update: Verify the code refers to the intended sheet cell and that D2 is not merged or otherwise unavailable for the write. If the sheet is protected, test with protection temporarily removed on a backup copy. Check that the event code has not been disabled by an earlier VBA error; close and reopen the workbook, then test again.

The caption changes but the color does not: Confirm the control is an ActiveX Toggle Button and that the code sets its BackColor property. Some display themes or control styles may make a color less noticeable. Use the ON/OFF caption and D2 status as the reliable indicators, and try another RGB color if the contrast is poor.

The code will not compile: In the Visual Basic Editor, choose Debug > Compile VBAProject if that command is available, and read the highlighted line. Check spelling, quotation marks, parentheses, and that the control name matches. If you pasted only part of the procedure, restore the complete code block and ensure it ends with End Sub.

The control is unavailable on your device: ActiveX controls are a Windows desktop Excel feature and are not supported in Excel for Mac or Excel for the web. Open the file in desktop Excel for Windows to use this specific solution. For a Mac or browser workflow, use a platform-supported input and adapt the automation instead of trying to run this ActiveX event.

Frequently Asked Questions

What is the difference between a toggle button and a regular button?
A toggle button retains an on/off value that changes with each click. A regular Form Control button typically runs an assigned macro but does not retain a pressed state. Choose a toggle when the user needs to switch a setting on and off.

Can I use a Toggle Button on a UserForm?
Yes. A UserForm can contain a Microsoft Forms ToggleButton control with its own Click event. The event code belongs in the UserForm’s code module, and references use the UserForm control name. The worksheet example above is specifically for an ActiveX control placed directly on a sheet.

Can I link the button directly to a cell?
This example writes a readable status to D2 in VBA rather than relying on a linked-cell setting. That makes the cell values explicit: Enabled when the button is on and Disabled when it is off. Change the range reference and text in the code to suit your workbook.

Can I use this VBA toggle button in Excel for Mac or Excel for the web?
No, not this ActiveX worksheet control. Excel for Mac does not support ActiveX controls, and Excel for the web does not run VBA macros. Use desktop Excel for Windows for the steps here, or choose a different, platform-supported interface for other users.

Why does the button’s color or state look different after reopening?
The control’s saved appearance and value may not match a status cell if another process changed one without updating the others. Test a fresh click after reopening and confirm the caption, color, button value, and D2 agree. If your workbook sets state in other code, add a synchronization step to that code as well.

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.