To create a toggle button in Excel VBA on Windows, choose Developer > Insert > Toggle Button under ActiveX Controls, turn on Design Mode, and add a click-event procedure for the control. Set its LinkedCell property if you want Excel to store the button’s ON/OFF state in a worksheet cell.
What a toggle button does
A toggle button is a two-state control: each click switches it between selected and unselected. In VBA, its Value property is Boolean: True when selected and False when not selected. That makes it useful for switches such as “Show details,” “Enable alerts,” or “Use advanced settings.” It remains in its chosen state until clicked again; unlike a standard command button, it is not just a momentary click action.
Excel’s ActiveX Toggle Button can respond to events, so code can run when a user clicks it. You can use that event to change the button caption, update a cell, or show and hide worksheet content. The control itself does not decide what “ON” should do. You define that behavior in VBA, which means it is important to make the visible state and the action agree.
This method is intended for desktop Excel for Microsoft 365 on Windows. ActiveX controls are not supported in Excel for the web or Excel for Mac, so the same control and event code will not work there.
Prepare the workbook and show Developer
Save the workbook as a macro-enabled Excel file before relying on VBA. Use File > Save As and select Excel Macro-Enabled Workbook (*.xlsm). A normal .xlsx file cannot retain VBA code when saved. If the Developer tab is missing, turn it on with File > Options > Customize Ribbon, check Developer in the Main Tabs list, and choose OK.
Before running any macro, save a separate copy of the workbook. VBA changes cannot be reversed with Ctrl+Z, so a backup is a practical safeguard while building or testing the control. You can open the Visual Basic Editor on Windows with Alt+F11. On Mac, Option+F11 may open it; some keyboards require Fn+Option+F11, but ActiveX controls themselves are not available in Excel for Mac.
- Use a workbook you trust and keep an unchanged copy while developing.
- Decide what the two states should mean, such as enabled and disabled.
- Choose a cell to hold the state if other formulas or procedures need to read it.
Insert the ActiveX Toggle Button
- Open the worksheet where the control should appear, then choose Developer > Insert.
- In the ActiveX Controls group, choose Toggle Button. The pointer changes so you can draw the control on the sheet.
- Drag on the worksheet to set its size. Leave the control selected and make sure Developer > Design Mode is turned on before editing it.
- Right-click the control and choose Properties. In the Properties window, set (Name) to a descriptive identifier, for example ToggleButton1, and set Caption to a clear starting label.
- Optionally set LinkedCell to a cell such as $B$2. The linked cell will reflect the control’s True or False state.
Use a name that is valid in VBA and easy to recognize. The name is different from the caption: the name identifies the object in code, while the caption is what the reader sees on the worksheet. For example, a control named ToggleButton1 might display “OFF” before it is selected. When you finish editing, turn Design Mode off to use the button normally.
Add the click-event code
With Design Mode on, right-click the toggle button and choose View Code. Excel opens the worksheet’s code module and creates or selects the control’s Click event. Put the procedure in that worksheet module, not in a standard module. The example below assumes the control is named ToggleButton1 and its LinkedCell property is $B$2. It changes the caption and writes a descriptive message to C2.
Private Sub ToggleButton1_Click()
Dim isOn As Boolean
' Read the True/False value from the linked cell.
isOn = CBool(Me.Range("B2").Value)
If isOn Then
Me.ToggleButton1.Caption = "ON"
Me.Range("C2").Value = "Feature enabled"
Else
Me.ToggleButton1.Caption = "OFF"
Me.Range("C2").Value = "Feature disabled"
End If
End Sub
Return to Excel and turn Design Mode off. Click the control and check that B2 changes between TRUE and FALSE, while C2 and the caption show the matching state. The code reads B2 because the LinkedCell property updates that cell as the button changes. If you use a different linked cell, change the B2 reference in the procedure to match it.
The first click updates the caption and message. If the button already has a saved selected state when the workbook opens, its caption may not yet match that state until the click event runs. For a polished workbook, initialize the caption and any related display when the sheet is activated or when the workbook opens; that requires a separate worksheet or workbook event procedure.
Use the linked cell for formulas and other code
A linked cell is a simple bridge between a control and the worksheet. When ToggleButton1 is selected, B2 contains TRUE; when it is not selected, B2 contains FALSE. You can refer to that cell in formulas, conditional formatting, or other procedures. For example, a formula can display a status based on B2, while a separate procedure can check the cell before carrying out a larger task.
Keep the linked cell in a predictable location and label it so another workbook user understands what it contains. You can hide the column or place the cell on a helper sheet, but avoid deleting the cell or moving it without updating the control’s LinkedCell property. If the cell is blank, contains an unexpected value, or is linked incorrectly, code that converts its contents to Boolean may fail or produce confusing results.
There are two reasonable ways to write the state to a cell: use the LinkedCell property, or set a cell’s value in the click-event code. For a basic True/False state, LinkedCell is convenient and avoids duplicating the control value in code. If you want custom text such as “Enabled” and “Disabled,” write that text to a different cell, as the example does in C2.
Make the control clear and usable
Do not make color the only signal that the button changed state. A caption that changes from OFF to ON gives a direct textual cue and is easier to interpret for users who cannot distinguish the chosen colors. Use concise captions, make the control large enough to click, and place a nearby label or instruction if the purpose is not self-evident.
Think through what should happen when the state changes back. If ON reveals rows or enables an input area, OFF should reverse that action or clearly explain why it does not. Avoid code that performs an irreversible action each time the button is clicked unless that behavior is deliberately designed; a toggle invites repeated switching. For actions with consequences, provide a clear status message and consider whether a confirmation is appropriate.
When a toggle affects several controls or cells, keep its event procedure short and move more involved work into a separate, clearly named procedure. That makes the click handler easier to inspect and reduces the chance that a future edit updates the button state but forgets a related worksheet action.
Common problems and how to fix them
- The button does nothing: turn Design Mode off to interact with an ActiveX control. If it still does nothing, check that macros are permitted for the workbook and that the Click procedure is in the worksheet module containing the control.
- “Method or data member not found” appears: compare the control’s (Name) property with the procedure name and code references. A control named ToggleButton1 needs an event procedure named ToggleButton1_Click.
- The cell does not change: inspect the LinkedCell property for a valid cell reference on the intended sheet. Check that the worksheet is not protected in a way that prevents the linked cell from being updated.
- The caption and cell disagree: confirm that the event reads the correct linked cell and that no other procedure is changing the cell or caption. The visible control value and its linked cell should be treated as one state.
- The control is missing or unusable: verify that the workbook is open in desktop Excel for Windows. ActiveX controls do not run in Excel for the web or Excel for Mac.
ActiveX controls can also be restricted by an organization’s security policy. Avoid changing global macro settings simply to make one workbook run. If a workbook is blocked, check its source and use the approved process for trusted files in your organization.
Alternatives and platform limits
If you need a clickable two-state input but do not need an ActiveX Click event, a Form Control check box linked to a cell can be a simpler Windows option: its linked cell reports a checked or unchecked state. A shape assigned to a macro is another way to create a custom-looking control, but the macro must manage the state itself. These alternatives have different behavior and are not drop-in replacements for an ActiveX Toggle Button.
On Mac or in Excel for the web, use a supported worksheet-based approach instead of ActiveX. A data-validation list containing values such as ON and OFF can provide a simple selectable state; it is not a clickable toggle control, but it works as ordinary cell input. A Form Control check box may also suit some desktop workflows, depending on the Excel platform and workbook requirements. If users must share the workbook across Windows, Mac, and web, test the chosen design on each platform before distributing it.
VBA itself is available in desktop Excel on Windows and Mac, but control support differs by platform. Therefore, having VBA code in a workbook does not mean every Windows worksheet control will be usable on a Mac or in a browser.
Save and secure the finished workbook
Keep the file in .xlsm format after adding the code, then close and reopen a saved copy to confirm that the control and procedure remain available. Excel may display a security warning when a workbook contains macros. Enable content only when you trust the workbook’s origin and understand what its code does; do not lower macro security for all files as a shortcut.
Before sharing, tell recipients that the workbook uses a Windows ActiveX control and VBA. If they open it in Excel for the web or on Mac, the control will not behave as it does in Windows desktop Excel. A short note explaining the ON/OFF meaning and the linked cell can also help recipients understand the workbook if macros are disabled.
Frequently Asked Questions
Can I create a toggle button in Excel without VBA?
A Form Control check box linked to a cell can represent a checked or unchecked state without VBA. It is not the same ActiveX Toggle Button, and it does not provide the same control-specific VBA Click event.
Why is the Toggle Button option missing?
On Windows, check Developer > Insert and look under ActiveX Controls. If you are using Excel for Mac or Excel for the web, ActiveX controls are not supported, so this control will not be available for use there.
How do I change the text on a toggle button?
Set the ActiveX control’s Caption property in Design Mode, or assign a new string to its Caption property in the Click event. Changing the caption does not change the control’s True or False value by itself.
Can a toggle button control a formula?
Yes. Set its LinkedCell property to a worksheet cell, then use that cell’s TRUE or FALSE value in a formula or other workbook logic. Keep the reference consistent if you move or rename the control.
Can I undo a VBA macro with Ctrl+Z?
No. Running VBA code clears Excel’s normal undo history, so Ctrl+Z cannot reverse the macro’s changes. Save a copy before running code that changes worksheet data.
Related
- Excel for Data Analysis: What It Can and Can’t Do
- Excel Kata for Data Analysis: Practice With Purpose
- Excel HP Data Analysis: How to Analyze Data on an HP PC
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Access VBA: How to Send EmailYes. Access VBA can send email either through DoCmd.SendObject, which uses the computer’s configured MAPI email client, or…
- Access VBA For Loop: Syntax, Examples, and Common ErrorsIn Access VBA, use a For...Next loop to repeat instructions a set number of times, and use For…
- VBA AutoFilter Not Blank: Filter Nonempty CellsTo use VBA AutoFilter to show only nonblank cells, set the field’s criterion to <>: dataRange.AutoFilter Field:=2, Criteria1:="<>".…
- VBA Code to Copy and Paste Data in Excel: ExamplesTo copy a range to another worksheet with VBA, use Range.Copy and specify the destination: sourceSheet.Range("A1:D20").Copy Destination:=destinationSheet.Range("A1"). This…
- VBA Range.Value: Reading and Writing Excel Cell DataIn Excel VBA, Range.Value reads or writes the value of a cell or range, such as a number,…
- Access VBA Date Functions: Dates, Times, and ExamplesAccess VBA provides built-in date and time functions for getting the current date, creating date values, adding or…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.