To insert a VBA button in an Excel worksheet, show the Developer tab, choose Developer > Insert > Button under Form Controls, and drag to draw the button. When Excel asks which macro to assign, select one or create it; clicking the button will then run that macro.
What a worksheet button does
A worksheet button gives someone a visible way to run a VBA macro without opening the Visual Basic Editor or using the Macros dialog. For example, a button might clear an input form, add a timestamp, prepare a report, or run a sequence of formatting steps. The button is an interface element; the VBA procedure it calls contains the instructions that do the work.
Excel offers different kinds of clickable objects, and the distinction matters. A Form Control button is usually the simplest choice for a standard macro button. An ActiveX Command Button offers additional event and property options on Windows, but it is not supported in Excel for Mac and can involve more setup. A worksheet shape can also be assigned to a macro, which is useful when appearance matters more than built-in button behavior.
Show the Developer tab
The Developer tab is often hidden by default in Excel for Microsoft 365 on Windows. To display it, go to File > Options > Customize Ribbon. Under Main Tabs, select Developer, then choose OK. The tab remains visible until you hide it again; enabling it changes the ribbon, not the workbook’s macro settings.
On a Mac, use Excel > Settings (or Preferences in some releases) > Ribbon & Toolbar, then enable Developer in the list of main tabs. Ribbon labels can vary slightly by Excel release. Excel for the web does not provide the desktop VBA editing and macro-running experience, so use the Excel desktop application for the steps below.
Create the VBA macro
Create or identify the macro before assigning it to the button. A typical button macro is a Public Sub with no required arguments, stored in a standard VBA module. In desktop Excel, choose Developer > Visual Basic to open the editor. In the editor, choose Insert > Module and add a procedure. The example below writes the current date and time into cell A1 on the active worksheet.
Public Sub AddRunTimestamp()
Dim ws As Worksheet
Dim runTime As Date
' Write the current date and time to A1 on the active sheet.
Set ws = ActiveSheet
runTime = Now
ws.Range("A1").Value = "Last run: " & Format(runTime, "yyyy-mm-dd hh:mm")
End Sub
This example deliberately uses ActiveSheet, so it affects whichever worksheet is active when the button is clicked. Cell A1 will be replaced, so choose a different cell or adjust the procedure if A1 contains important data. If the macro should always update a particular tab, you can refer to a named worksheet instead, but the sheet name in the code must match the workbook.
Save a copy of the workbook before running a macro. VBA actions generally cannot be reversed with Ctrl+Z, so a mistake may require closing without saving or restoring a backup. The sample changes one cell, but other macros can make broader changes.
Insert the Form Control button and assign the macro
- Return to the worksheet where the button should appear and select Developer > Insert.
- In the Form Controls section of the menu, select Button (Form Control). Do not select an ActiveX control unless you specifically need ActiveX features.
- Drag across the worksheet to set the button’s size. The Assign Macro dialog should appear when you release the mouse.
- Select AddRunTimestamp from the list and choose OK. If you have not made the macro yet, cancel the dialog, create it in a standard module, then repeat the button steps.
- Click a blank cell to finish. Click the button to run the assigned macro.
To change the displayed wording, right-click the Form Control button, choose Edit Text, and type a label such as “Add timestamp.” Click outside the button when finished. To move or resize it, right-click it and use the sizing handles; click away afterward to avoid leaving the control selected. On a Mac, the equivalent secondary-click can be performed with the configured right-click gesture or Control-click.
Choose the right kind of button
For most straightforward tasks, use a Form Control button. It is designed to run an assigned macro, is comparatively simple to set up, and works in desktop Excel on Windows and Mac. It does not provide the same event-driven customization as an ActiveX control, but many workbook buttons do not need that complexity.
- Form Control button: A good default for running a macro from a worksheet. Assign or change its macro through the context menu.
- ActiveX Command Button: A Windows-only option with additional properties and event code. It can require Design Mode and more VBA knowledge, so avoid it for a workbook that must work on Mac.
- Shape assigned to a macro: A shape can be styled more freely. Insert a shape from Insert > Shapes, then right-click it and choose Assign Macro. This is useful for a branded or custom-looking control.
Do not confuse inserting a button with embedding the VBA code in the button itself. With a Form Control, the button stores a link to a macro; the procedure remains in the workbook’s VBA project. If the macro is renamed or removed, the button’s assignment may need to be updated.
Save and share the workbook safely
A workbook containing VBA needs a format that preserves macros. Use File > Save As and select Excel Macro-Enabled Workbook (*.xlsm), or use the binary workbook format (*.xlsb) if that suits your workflow. Saving a macro workbook as the standard .xlsx format removes its VBA project. Excel normally warns about this when you try to save, but choose a macro-enabled format if the code must remain available.
Recipients need desktop Excel with macros allowed to run the VBA. Excel for the web can open many macro-enabled workbooks, but it cannot run, create, or edit VBA macros. A button may be visible in a browser without being able to perform its VBA action. Tell recipients what the macro does and which desktop version they need rather than assuming the button will work everywhere.
Macros can change or delete workbook data, so do not enable code from an unknown or untrusted file. Excel’s security settings may block macros, especially for files downloaded from the internet. Follow your organization’s security policy; do not lower global macro protections just to make one button work. For a legitimate workbook, verify its source and use an approved trusted location or other approved method if your organization requires it.
Common problems and fixes
- The Developer tab is missing: Enable it in Excel’s ribbon settings as described above. This is a display preference, separate from whether a macro is permitted to run.
- The macro does not appear in Assign Macro: Check that the procedure is in a standard module, is declared Public, and has no required parameters. Also confirm the code is in the workbook you are assigning the button in.
- The button does nothing or Excel reports that macros are disabled: Confirm the file is saved as .xlsm or .xlsb, open it in desktop Excel, and check the macro security message and your organization’s policy. A visible button alone does not mean the macro is enabled.
- The wrong sheet changes: The example uses ActiveSheet. Select the intended sheet before clicking, or edit the code to refer to a specific worksheet by name.
- The button is difficult to select or move: Right-click the Form Control to open its context menu, then use the handles or Edit Text. If the worksheet is protected, protection settings may prevent changing drawing objects; unprotect it only if you are authorized and know the password.
- The macro disappeared after saving: Check whether the file was saved as .xlsx. Save a copy in a macro-enabled format before making further changes.
Alternatives when a VBA button is not suitable
If the task can be done with ordinary worksheet formulas, a formula may be easier to share because it does not require macro permissions. If the goal is simply to navigate within a workbook, a hyperlink or a shape linked to a cell may be enough. For repeatable automation that must work in Excel for the web, investigate Office Scripts if they are available in your Microsoft 365 environment; Office Scripts are separate from VBA and have different capabilities and setup.
For a one-click desktop action, a worksheet button is convenient. For a process used by many people, consider whether a button is the best interface: document what it changes, identify its input cells, and provide a safe way to test it. A clear label such as “Refresh report” is more useful than a generic “Button 1,” particularly when the workbook is shared.
Frequently Asked Questions
Can I add a VBA button in Excel for the web?
No. Excel for the web does not create or run VBA macros. Open the workbook in desktop Excel to insert the button and run its macro; VBA actions will not work in the browser.
Why is my macro not listed when I assign the button?
It may be in the wrong place or have a form Excel cannot assign. Put a Public Sub with no required arguments in a standard module, and make sure the macro belongs to the workbook you are editing.
Does a worksheet button work on a Mac?
A Form Control button can be used with VBA in desktop Excel for Mac, although some ribbon details differ. ActiveX controls are not supported on Mac. The workbook also needs to be saved in a macro-enabled format, and macros must be permitted.
Can I use a shape instead of a button?
Yes. Insert a shape, right-click it, choose Assign Macro, and select the procedure. Shapes offer more styling flexibility, while a Form Control button provides a familiar button control with a direct macro assignment.
Why does saving as .xlsx remove my button’s macro?
The .xlsx format does not preserve VBA code. Save the workbook as .xlsm or .xlsb when it needs to retain its VBA project, and keep a separate backup before changing formats.
Related
- Excel Heat Map by ZIP Code: Create and Troubleshoot
- Excel Heat Map Colours: How to Apply and Choose Them
- Excel Heat Map Conditional Formatting: Setup and Tips
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Make String Uppercase with UCase: Examples and TipsTo make a string uppercase in VBA, use the built-in UCase function: assign UCase("hello") to a variable and…
- Move a Picture in an Excel UserForm with the MouseTo move a picture around an Excel UserForm with the mouse, handle the Image control’s MouseDown and MouseMove…
- VBA Sort Reference Is Not Valid: Causes and Fixes“The sort reference is not valid” is an Excel sorting error, not a VBA syntax rule. In a…
- VBA Range Type: What It Means and How to Use It in ExcelIn Excel VBA, Range is an object type: it represents one cell, a group of cells, or a…
- Named Range in VBA: Create, Read, and Manage Excel NamesIn Excel VBA, a named range is a defined name that refers to a cell, a range of…
- Excel VBA Collection: How It Works and When to Use ItAn Excel VBA Collection is an in-memory container that holds related items and lets you retrieve them by…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.