If you mean a tiny on/off control that runs a macro, Excel has no built-in feature called a “Macro Tiny Toggle.” You can make one by assigning a short VBA macro to a small shape or Form Control button; clicking it can switch a workbook setting on and off.
What “macro tiny toggle” can mean
The phrase is not a standard Excel feature name, so it may describe one of several things: a compact button that launches a macro, a control whose appearance suggests an on/off switch, or a macro that alternates between two states. The practical distinction is that a button runs an action, while a toggle both runs an action and changes state each time it is clicked.
For a simple, compact control, a small shape assigned to a macro is often the most straightforward choice. The macro can check the current setting, reverse it, and optionally update the button label. If you only need to store a visible TRUE/FALSE value, a Form Control checkbox linked to a cell may be enough; no VBA is required just to change that cell.
A simple example: show or hide a column
Here is a small toggle macro for Microsoft Excel for Microsoft 365 on Windows. It hides column D on a worksheet named Data if that column is visible, and shows it if it is already hidden. Put the button on a different sheet, such as Control, so it remains easy to reach after column D is hidden.
Before using the code, replace Data with the exact name of the worksheet whose column you want to toggle. Worksheet names must match, including spaces. For example, if the tab says Monthly Report, use that name in the code instead.
Sub ToggleColumnD()
Dim ws As Worksheet
' Change Data to the name of your target worksheet.
Set ws = ThisWorkbook.Worksheets("Data")
' Reverse the current hidden state of column D.
ws.Columns("D").Hidden = Not ws.Columns("D").Hidden
End Sub
The macro uses the column’s existing Hidden property as its state, so it does not need a separate indicator cell. Clicking once hides column D; clicking again shows it. This is useful for temporarily hiding detail columns in a report, but it is not a security feature: hiding a column does not protect its contents.
How to add a small clickable control
- Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm). Use File > Save As, choose the file type, and save a copy before experimenting.
- Open the VBA editor from Developer > Visual Basic. In the editor, choose Insert > Module, then paste the macro into the new module. On Windows, Alt+F11 is the usual shortcut; on a Mac, use Developer > Visual Basic or the keyboard shortcut available for your keyboard, which may require Option and Fn with F11.
- Return to Excel and select Insert > Shapes. Choose a small rectangle or rounded rectangle, draw it on the Control sheet, and type a useful label such as “Toggle details.”
- Right-click the shape and choose Assign Macro. Select ToggleColumnD and confirm. On a Mac, Control-clicking the shape opens its context menu.
- Click outside the shape to finish editing it, then click it once to test. Check that column D on Data hides, and click again to confirm it returns.
To make the control visually compact, resize the shape using its handles and shorten the label. Keep it large enough to click reliably and give it a clear label; a tiny unlabeled square can be hard to find and can be mistaken for a decorative mark.
Choosing between a shape, button, and checkbox
There is more than one way to create a macro control. The best choice depends on whether the user needs a clear click target, a persistent on/off indication, or merely a cell value.
- Shape assigned to a macro: Easy to resize and format, and a good fit for a compact custom control. It does not automatically display whether the setting is on or off; the macro must change its label or appearance if you want that feedback.
- Form Control button: A conventional button that can run an assigned macro. In Excel for Windows, use Developer > Insert > Form Controls > Button, draw it, and choose the macro when prompted. It is simple, but its appearance is less flexible than a shape.
- Form Control checkbox: A familiar on/off control that can be connected to a worksheet cell. Right-click it, choose Format Control, and use the Control tab to set the Cell link. The linked cell shows TRUE when checked and FALSE when cleared. A checkbox changes that cell without requiring a macro; use VBA only if a click must also perform another action.
- ActiveX Toggle Button: This is a separate control in some Windows desktop versions of Excel, but ActiveX controls are not supported on Mac or in Excel for the web. For a workbook meant to work across platforms, a shape or Form Control is usually a more portable starting point.
A Form Control checkbox is not the same as a dedicated, animated switch. If the visual state matters, test the exact control and workbook version you plan to distribute. A linked cell provides a reliable way to inspect a checkbox’s state, but the macro still needs logic to respond to that state if the workbook must carry out an action.
Showing the current state on the control
A control that changes something but gives no indication of its current state can confuse users. For the column example, users can check whether column D is visible, but a stronger interface could change the shape’s text between “Hide details” and “Show details.” That requires a macro to update the shape’s text as well as the column’s Hidden property.
Another approach is to keep an indicator in a cell, such as Control!B2, and have the macro update that cell whenever it changes the setting. This can help when a report needs a visible status label or when other workbook logic needs to read the chosen state. Avoid keeping two independent indicators—such as a linked checkbox and a separate status cell—unless your code updates both consistently. Otherwise, the displayed state can drift away from the actual setting.
Common problems and what to check
- The button does nothing: Confirm that the workbook is saved as .xlsm, that macros are enabled for this trusted file, and that the control is assigned to the intended macro. Excel for the web cannot run VBA macros; open the file in the desktop app.
- Excel reports that a worksheet does not exist: Check the tab name against the text inside Worksheets(“Data”). A renamed tab or a misspelled name prevents the macro from finding it.
- The button disappears when you hide something: If the macro hides the worksheet or row containing the button, you may lose access to the control. Put the control somewhere that remains visible.
- A checkbox seems out of sync: Check the linked cell in Format Control and make sure it is not being changed by another formula or macro. A checkbox can show its own checked state while separate code changes a different setting.
- The control is difficult to click: Enlarge it slightly, use a short label, and keep it away from cell edges or other objects. “Tiny” should mean compact, not so small that users miss it.
If you use a shape, make sure it has not been placed over a cell or object that should remain clickable. You can move or resize it after creation, and you can reopen Assign Macro from its context menu if you later rename the macro.
Security, compatibility, and saving
VBA can change workbook content, so Excel may block macros from files downloaded from the internet or received from an untrusted source. Only enable macros in a workbook when you trust its origin and understand what the code does. Do not instruct users to lower Excel’s macro security settings globally just to make one button work.
Save a backup before running or changing a macro. Actions performed by VBA cannot be reversed with Ctrl+Z, and closing without saving is not a safe substitute for a backup if the file has other unsaved work. Excel for Windows and Excel for Mac can run many VBA macros, though some controls and platform-specific code differ. Excel for the web does not run VBA, so recipients who need the toggle must use a supported desktop version.
Form Control buttons and checkboxes are distinct from ActiveX controls. ActiveX is Windows-specific and can bring extra compatibility and security considerations. If the workbook will be shared with Mac users, keep to standard VBA and a shape or Form Control, then test the actual workflow on the target platform.
Alternatives when VBA is unnecessary
If the goal is simply to show or hide rows or columns occasionally, Excel’s built-in Hide and Unhide commands may be simpler than a macro. Select the relevant rows or columns, right-click, and choose Hide; to restore them, select the neighboring rows or columns and choose Unhide. This requires no macro-enabled file.
If you want users to choose a status that formulas can read, a checkbox linked to a cell is often sufficient. If you want a compact control that performs a repeatable action with one click, a shape assigned to a macro is a good fit. For automation that must work in a browser, consider a non-VBA workflow supported by Excel for the web, such as Office Scripts where available, rather than relying on a VBA button.
Frequently Asked Questions
Does Excel have a built-in “tiny toggle” macro?
No. “Tiny toggle” is not a standard Excel command or named feature. It usually means a small control that runs code to switch a setting between two states.
Can I make a toggle without VBA?
Yes, if you only need an on/off value. Insert a Form Control checkbox and set its linked cell; the cell will show TRUE or FALSE. Use VBA only when changing the checkbox also needs to perform a macro action.
Can a shape run a macro in Excel?
Yes. Insert a shape, right-click it, choose Assign Macro, and select a macro in the workbook. The shape is a clickable object, not a control that automatically remembers an on/off state.
Will the macro work in Excel for the web?
No. Excel for the web does not run VBA macros. Open the macro-enabled workbook in the Excel desktop app to use a VBA-based toggle.
Why can’t I undo the macro with Ctrl+Z?
Excel’s normal undo history does not generally cover changes made by VBA. Save a separate copy before testing a macro, especially if it changes or deletes workbook data.
Related
- How to Sum Just Subtotals in Excel Without Double-Counting
- How to Use SUBTOTAL in Excel
- X-Arcade Programming Software: What You Need to Know
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA: Delete a Row from a TableTo delete a data row from an Excel table with VBA, use the table’s ListRows collection: for example,…
- Excel VBA AutoFilter Multiple Columns: Examples and TipsTo filter several Excel columns with VBA, call AutoFilter once for each field, using the same data range.…
- VBA Error Handling: On Error, Cleanup, and Debugging TipsVBA error handling lets a macro respond when a statement fails instead of stopping unexpectedly. Use On Error…
- VBA UserForm References: How to Show and Control FormsIn Excel VBA, reference a UserForm by its form class name, such as UserForm1, or create an explicit…
- Excel VBA: Stop an Endless Loop and Recover SafelyTo stop a running Excel VBA loop on Windows, press Ctrl+Break (on some keyboards, use Ctrl+Fn+Pause), then choose…
- Excel Macro Commands List: Common VBA Actions ExplainedAn Excel macro command is a Visual Basic for Applications (VBA) instruction that tells Excel what to do,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.