In Excel for Microsoft 365 on Windows, an ActiveX Toggle Button runs VBA from its Click event: insert the control, open its code window, and add the action you want it to switch on and off. If you have a regular Form Control button instead, assign it a macro from its right-click menu; the macro can perform a toggle action even though the button itself does not stay pressed.
What You’ll Need
- Excel for Microsoft 365 desktop on Windows. ActiveX controls do not work in Excel for the web or Excel for Mac.
- A workbook saved as an Excel Macro-Enabled Workbook (
.xlsm) so it can retain VBA code. - The Developer tab. If it is not visible, go to File > Options > Customize Ribbon, select the Developer check box in the right-hand list, and choose OK.
- A clear idea of what the on and off states should do—for example, show or hide a set of detail columns.
Step-by-Step
- Save a macro-enabled copy first. Choose File > Save As, select Excel Macro-Enabled Workbook (*.xlsm), and save the workbook. If you already have unsaved changes, save those too. Keep a separate backup before running VBA: macros cannot be undone with Ctrl+Z, and a macro may change or remove workbook content.
- Choose the kind of button you have. For a genuine on/off control that stays visually pressed, use an ActiveX Toggle Button. It runs code through a control event; there is no standard Assign Macro command for that control. A Form Control button or a shape can be assigned a macro, but it will not retain a pressed state by itself. Use the first method for a true toggle, or the alternative in step 8 if you specifically need the Assign Macro dialog.
- Insert an ActiveX Toggle Button. On the Developer tab, choose Insert. In the ActiveX Controls section—not Form Controls—select Toggle Button, then click or drag on the worksheet to place it. Put it somewhere that will not be hidden by the action; for example, do not place it in columns you plan to hide. If you do not see the ActiveX Controls section, check that you are using Excel desktop on Windows.
- Give the control a useful label. While Developer > Design Mode is on, select the button and open Developer > Properties. In the Properties window, set the
Captionproperty to a starting label such asShow details. You can change the caption in code so that it describes the next action. The control’s name, oftenToggleButton1, is different from its caption; note the name shown in the Properties window. - Open the button’s Click event. Leave Design Mode on, then right-click the toggle button and choose View Code. Excel opens the code module for that worksheet and creates a procedure named for the control, such as
ToggleButton1_Click. Put the code in this procedure, not in a regular worksheet cell or a standard module. If Excel does not create the procedure, select the control name in the left drop-down andClickin the right drop-down at the top of the code window. - Add the on/off action. The example below uses the button’s Value to show columns B:D when the button is on and hide them when it is off. It also updates the caption. Replace the column range if your detail is elsewhere. The worksheet button must be outside the columns being hidden so you can still click it.
Private Sub ToggleButton1_Click()
Dim ws As Worksheet
Set ws = Me
' Show details when pressed; hide them when released.
If Me.ToggleButton1.Value Then
ws.Columns("B:D").Hidden = False
Me.ToggleButton1.Caption = "Hide details"
Else
ws.Columns("B:D").Hidden = True
Me.ToggleButton1.Caption = "Show details"
End If
End Sub
This is an event procedure, so clicking the ActiveX control calls it automatically. It is not necessary—or possible through the normal Form Control Assign Macro dialog—to assign this event procedure to the ActiveX toggle. If your control has a different name, replace both instances of ToggleButton1 with the name shown in Properties. The worksheet code name Me refers to the sheet that contains the control.
- Return to the sheet and test both states. Close or switch away from the Visual Basic Editor, then turn off Developer > Design Mode. Click the button once: the button should appear pressed, columns B:D should show, and the caption should read
Hide details. Click again: the button should release, the columns should hide, and the caption should return toShow details. If the button only selects or moves, Design Mode may still be on. - Use Assign Macro for a regular button, if that is what you meant. Instead of an ActiveX Toggle Button, choose Developer > Insert > Button (Form Control), draw it on the sheet, and select a macro in the Assign Macro dialog. To create a macro that switches column visibility, open Developer > Visual Basic, choose Insert > Module, and add the following code. Change
Summaryto the exact name of your worksheet tab.
Public Sub ToggleDetails()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Summary")
' Switch the detail columns between visible and hidden.
ws.Columns("B:D").Hidden = Not ws.Columns("B:D").Hidden
End Sub
Close the Visual Basic Editor. Right-click the Form Control button, choose Assign Macro, select ToggleDetails, and choose OK. Clicking it alternates the column visibility. This simple macro uses the columns’ current hidden state as its memory; the button itself does not look pressed. If you need a persistent on/off appearance as well, use the ActiveX event method or build a more advanced macro that updates a cell or button caption to reflect the state.
When you share the workbook, tell recipients that they need desktop Excel with macros enabled to run the VBA. Excel may show a security warning when the file opens; users should only enable macros for a workbook they trust. Excel for the web can open an .xlsm file but cannot run its VBA macros.
Common Mistakes
- Choosing the wrong control type. A Form Control button does not have a built-in pressed state or a Click event code window. An ActiveX Toggle Button uses its event procedure and does not use the ordinary Assign Macro dialog. Check the section headings in Developer > Insert before adding the control.
- Leaving Design Mode on. With Design Mode enabled, clicking an ActiveX control selects it for editing instead of running its code. Turn off Developer > Design Mode before testing.
- Putting code in the wrong place. The ActiveX example belongs in the worksheet module and must have the matching control name in its procedure. The regular macro belongs in a standard module. A procedure in the wrong module, or a name mismatch, may not run from the control.
- Saving as a macro-free workbook. Saving as
.xlsxremoves VBA code. Save as.xlsmand check the file extension after saving. - Hiding the control or its target data. If the button is positioned in a column that the macro hides, you may lose access to it. Also make sure the columns you hide do not contain information users still need to see.
- Assuming the button’s starting state matches the sheet. If the columns start hidden but the toggle appears released, the display and control state can disagree. Decide on a starting state and make the sheet, button Value, and caption consistent before distributing the workbook.
Tips
- Use labels that describe the next action, such as
Show detailswhen the details are hidden andHide detailswhen they are visible. This is clearer than a label that only saysOnorOff. - Test the button after closing and reopening the saved workbook. This checks both that the VBA was saved and that the initial control state makes sense.
- Keep a backup before running a new or edited macro. VBA actions are not recorded in Excel’s undo history, so Ctrl+Z will not reverse them.
- Use the narrowest practical range in your code. For example, hiding B:D is easier to review than hiding entire rows or a large part of the worksheet.
- If other people use the file, add a nearby note explaining what the toggle changes and how to restore the original view.
Troubleshooting
- Clicking does nothing: Turn off Developer > Design Mode and try again. If it still does not work, confirm the workbook is open in Excel desktop on Windows, that macros are enabled for this trusted file, and that the file is saved as
.xlsm. - Excel reports that it cannot find the macro: This most often applies to a Form Control button. Open Assign Macro again and select the exact public macro name. Confirm the macro is in a standard module, is declared
Public Sub, and has no required arguments. For an ActiveX button, check the matching worksheet event procedure instead of using Assign Macro. - The ActiveX code window is blank or the procedure does not run: Confirm the code is in the sheet module that contains the control. Check that the procedure name matches the control’s name and ends in
_Click. Use the code-window drop-downs to create the correct Click event if needed. - The wrong columns change, or Excel shows a worksheet-name error: In the Form Control example, replace
Summarywith the exact worksheet tab name, including spaces. In the ActiveX example, changeB:Dto the intended range. Ensure the target worksheet is not protected in a way that prevents the macro from changing column visibility. - The columns change but the toggle does not: That is expected for the Form Control version: it performs a toggle action but has no latched visual state. Use an ActiveX Toggle Button on Windows if the pressed appearance matters. ActiveX controls are not available on Mac or in Excel for the web.
- Excel blocks ActiveX controls: Check whether your organization’s security policy disables ActiveX, and do not lower security settings for an unknown file. If ActiveX is unavailable or restricted, use a Form Control button assigned to a macro, or use a worksheet cell and formula-based display instead.
Frequently Asked Questions
Can I use Assign Macro with an ActiveX Toggle Button?
No. An ActiveX Toggle Button runs code from its worksheet Click event. Use Developer > Insert > Button (Form Control) if you need the Assign Macro dialog; it will run a macro but will not stay pressed.
How do I make the toggle run a different action when it is on or off?
Read the ActiveX control’s Value property in its Click event. Use an If...Then...Else statement to perform one action when the value is True and another when it is False, as in the show-and-hide example.
Why does the toggle button only select instead of running?
Developer > Design Mode is probably on. Turn it off to operate the control. If it still does not respond, check that the file is an .xlsm workbook and that macros are allowed.
Will the macro work in Excel for Mac or Excel for the web?
ActiveX controls are Windows-only, and Excel for the web does not run VBA. Excel for Mac can run some VBA macros but does not support ActiveX controls; use a supported Form Control or another Mac-compatible interface and verify the workbook on that platform.
Can I undo what the macro did with Ctrl+Z?
No. Running VBA clears Excel’s normal undo history, so Ctrl+Z cannot reverse the macro’s changes. Save a backup before testing or running code that changes the workbook.
Why does the button show the wrong state when I reopen the file?
The control’s saved Value, caption, and worksheet appearance may not agree. Set a consistent initial state before saving, and test a close-and-reopen cycle. A Form Control button has no persistent pressed state unless you add code to store and display one.
Related
- Grade 9 Balancing Chemical Equations, Step by Step
- Java Enum Values: Meaning, Methods, and Examples
- Bullseye Glass Kiln Firing Schedules: A Practical Guide
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Dictionary as a Public Constant? What Works in ExcelNo: a VBA Scripting.Dictionary cannot be declared as a Public Const, because VBA constants must hold compile-time values…
- VBA Copy PasteSpecial: Values, Formats, and MoreIn Excel VBA, copy a range and call PasteSpecial on the destination to paste only values, formats, formulas,…
- VBA Sort Table: Excel Code, Options, and Common IssuesTo sort an Excel table with VBA, get the table as a ListObject, add one or more sort…
- Excel VBA Boolean: True, False, and How to Use ThemYes—Excel VBA has a built-in Boolean data type for values that are either True or False. Declare a…
- Excel Code to Insert a Row: VBA and ShortcutsTo insert a worksheet row with Excel VBA, use Rows(ActiveCell.Row).Insert Shift:=xlDown in a macro; it inserts a row…
- VBA MsgBox Font Size: What You Can and Cannot ChangeYou cannot set the font size of VBA’s built-in MsgBox in Excel: the function has no font-size argument…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.