VBA Toggle: Make a Button Switch a Setting On or Off

Yes. You can turn a VBA macro into an on/off toggle by assigning it to a clickable worksheet shape and having the macro switch a setting each time you click. In the example below, one click turns worksheet gridlines off and the next turns them back on; the same pattern can control other reversible actions.

What a VBA toggle does

A toggle alternates between two states: on and off, shown and hidden, enabled and disabled. Unlike a one-way macro that always hides a column or applies a format, a toggle checks the current state and performs the opposite action. Click it repeatedly and the result alternates.

There are two parts: something the user clicks, and VBA that reads or stores the state. A worksheet shape is a convenient click target because you can format it like a button, give it a clear label, and assign a macro to it. The macro can also update the label so the control reports the state it just set.

This is useful for reversible presentation and workflow actions, such as hiding helper columns, showing a detail section, changing a display setting, or switching a report between two views. A toggle is not automatically a special VBA feature; it is a small design pattern built from a clickable control and code.

Choose a shape, Form Control, or ActiveX control

For most workbooks, use a shape or a Form Control button assigned to a macro. Shapes are easy to format and are a practical choice for a dashboard or report. Form Control buttons also run assigned macros, but changing their captions uses a different VBA object model than changing a shape’s text.

ActiveX ToggleButton controls have a built-in Value property that represents a pressed or unpressed state, which can suit a form with several controls. However, ActiveX controls are Windows-specific and can be restricted by Office security settings. They are not the right choice for a workbook that needs to work on Mac or Excel for the web. A shape-driven toggle is simpler to share across Excel desktop installations.

Excel for the web does not run VBA macros. A workbook can be opened there, but the macro-based toggle will not operate in the browser. If browser use is essential, consider an Office Script or a non-macro worksheet design instead.

Create a clickable toggle in Excel

In Excel for Microsoft 365 on Windows, save a copy of your workbook before running a macro; VBA actions cannot be reversed with Ctrl+Z. For a workbook that will contain VBA, use File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm). Then follow these steps:

Open the Developer tab and select Visual Basic. If Developer is not visible, enable it through File > Options > Customize Ribbon.
In the Visual Basic Editor, choose Insert > Module, then paste the example code in the module.
Return to the worksheet. Select Insert > Shapes, draw a shape, and type an initial label such as Gridlines: ON or Gridlines: OFF to match the current worksheet display.
Right-click the shape, choose Assign Macro, select ToggleGridlines, and click OK.
Click the shape to switch gridlines. Click again to restore them. The shape label changes after each click.

The example uses the active worksheet and the shape that called the macro. Before running it, save a copy of the workbook: macros cannot be undone with Ctrl+Z.

Public Sub ToggleGridlines()
    Dim ws As Worksheet
    Dim toggleShape As Shape
    Dim gridlinesAreVisible As Boolean

    ' The clicked shape must be on the active worksheet.
    Set ws = ActiveSheet
    Set toggleShape = ws.Shapes(CStr(Application.Caller))

    ' Reverse the active window's current gridline setting.
    gridlinesAreVisible = Not ActiveWindow.DisplayGridlines
    ActiveWindow.DisplayGridlines = gridlinesAreVisible

    ' Keep the shape label in step with the setting.
    If gridlinesAreVisible Then
        toggleShape.TextFrame2.TextRange.Text = "Gridlines: ON"
    Else
        toggleShape.TextFrame2.TextRange.Text = "Gridlines: OFF"
    End If
End Sub

How the example works

Application.Caller identifies the shape that launched the macro. The code uses that name to find the corresponding shape on the active sheet, so the macro can change the clicked shape’s text. This is why the macro is assigned to the shape itself rather than being run from the macro list.

The key line reads the current setting and negates it: if gridlines are visible, the new value is False; if they are not visible, the new value is True. The next line applies that value to ActiveWindow.DisplayGridlines. Finally, the If statement sets the label to match the new state. The macro relies on the actual display setting, not on the button text, so a label that starts out of sync corrects itself after the first click.

Gridlines are a display option, not cell borders. Turning them off does not remove borders that have been applied to cells, and turning them back on does not add borders. The setting affects the worksheet view in the active Excel window; it does not change the workbook’s underlying cell values or formatting.

Adapt the pattern to a different action

For another toggle, keep the same basic sequence: read the current state, reverse it, perform the matching action, and update the control’s label. For example, a macro could show or hide a group of columns. It should check whether those columns are currently hidden before changing their Hidden property, then label the button Show details or Hide details as appropriate.

For a toggle that changes a report between two views, the two branches might apply different filters or show different worksheet sections. Be precise about what each state means. A button labelled “ON” is not helpful if users cannot tell what is on, so a label such as “Details: SHOWN” communicates more.

Before adapting the code, decide what should happen if the workbook is already in an unusual state. For example, if users can manually hide some columns, a macro that assumes every column is either all visible or all hidden may produce confusing results. Checking the relevant range or using a dedicated state cell can make the behavior more predictable.

Where the toggle state should live

The gridline example reads a real Excel setting each time, which avoids storing a second, potentially inaccurate copy of the state. This is usually best when Excel already exposes the property you want to switch. If the action has no convenient property to inspect, store the state deliberately.

A module-level Boolean variable can hold a state while the VBA project remains loaded, but it resets when the project is reset or the workbook is closed and reopened. That can make the next click behave unexpectedly if the workbook’s visible state remains changed while the variable returns to its default. It is suitable for temporary operations where a reset is acceptable, not for state that must survive reopening.

For a persistent toggle, a dedicated cell can store a value such as TRUE or FALSE, or a named range can identify that cell. The macro reads the stored value, updates it, and applies the corresponding action. You can keep the cell on a protected or hidden settings sheet, but document that choice for anyone maintaining the workbook. Avoid using a cell that users may edit as ordinary data.

Common problems and fixes

The shape does nothing: confirm that the workbook is saved as .xlsm, macros are allowed for this trusted file, and the intended macro is assigned through Assign Macro. Excel for the web will not run it.
The macro reports a missing shape: the example expects to be launched by a shape on the active worksheet. Assign it to that shape; running it directly from the Visual Basic Editor does not provide the same shape caller.
The label and setting disagree: set the label to match the worksheet when you first create the shape. The code corrects it after a click, but it does not initialize the label automatically when the workbook opens.
The action changes the wrong sheet or window: this sample uses ActiveSheet and ActiveWindow. A more complex workbook may need explicit worksheet references, especially if code activates other sheets or multiple windows are open.
Ctrl+Z does not undo the change: that is expected for VBA macros. Save a copy before testing, and consider writing a separate reverse action if users need a deliberate way to restore more complex changes.

Security and platform considerations

Macros can change workbook contents, so only enable them in files from a source you trust. On Windows, macro controls are available in File > Options > Trust Center > Trust Center Settings > Macro Settings. The safest choice depends on your organization’s policies; do not enable all macros simply to make one button work. Files downloaded from the internet may also be blocked by Windows or managed security settings.

Excel for Mac supports VBA, but ActiveX controls are not available there. A shape assigned to a macro is a more suitable desktop option when sharing between Windows and Mac, though menus and some interface details differ. Excel for the web does not execute VBA, so recipients who need to click the toggle must use Excel desktop. Always test a shared workbook in the versions and environments its users actually have.

Frequently Asked Questions

Can one VBA button switch between two macros?
Yes. Assign one macro to the button and have it inspect the current state, then call or perform the appropriate action. That keeps the user-facing control to one click target while the code decides which branch to run.

Can I make a toggle button without VBA?
Yes, if the task is simple. A checkbox or data-validation list can represent an on/off choice, and formulas or conditional formatting can respond to it. Those options do not run arbitrary VBA actions and can be more compatible with Excel for the web.

Why does my toggle reset when I reopen the workbook?
A VBA variable stored only in memory is reset when the VBA project closes or resets. Store the state in a worksheet cell or read it from the real Excel setting if it needs to persist or stay aligned with the workbook after reopening.

Will a VBA toggle work in Excel for the web?
No. Excel for the web does not run VBA macros. Use Excel desktop for the macro, or consider an Office Script or a formula-based control if browser support is required.

Can I use the same toggle on Mac?
A shape assigned to a macro is generally the better cross-platform choice than an ActiveX ToggleButton. Excel for Mac supports VBA, but the workbook still needs to be tested there, and VBA macros do not run in Excel for the web.

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.