Excel VBA Button: Add, Assign, and Fix Macro Buttons

An Excel VBA button is a clickable control that runs a macro in a workbook. In Excel for Microsoft 365 on Windows, the simplest option is a Form Control button: enable the Developer tab, choose Developer > Insert > Form Controls > Button, draw the button, and assign it to a macro.

What an Excel VBA button does

A button gives workbook users a visible way to run a particular macro without opening the Visual Basic Editor or locating the macro in a list. For example, a button might refresh a report, add a timestamp, prepare a printable view, or transfer entered values to another sheet. The button itself is not the VBA code; it is a control connected to a procedure that contains the code.

That distinction is useful when diagnosing a workbook. A button can be present but do nothing if its macro is missing, unavailable, blocked by security settings, or assigned incorrectly. Conversely, a macro can work when run from the Macro dialog even though the button is not connected to it.

Before you add a button

First make sure the workbook is saved in a format that supports VBA. Use Excel Macro-Enabled Workbook (*.xlsm), or another macro-supporting format such as *.xlsb. An ordinary *.xlsx file cannot store VBA code. If you save a macro-enabled workbook as *.xlsx, Excel warns that VBA content will be removed; do not continue with that conversion if you need the macro.

Also decide what the button should do and who will use it. A button that clears cells, overwrites data, or changes many worksheets deserves extra care, because Excel does not provide a normal Undo history for macro actions. A button that simply refreshes or displays information may have fewer consequences, but its effect should still be clear to users.

If the Developer tab is not visible in Excel for Windows, open File > Options > Customize Ribbon, select the Developer check box under Main Tabs, and choose OK. On Mac, enable Developer from Excel > Preferences or Settings > Ribbon & Toolbar, depending on the Excel version. The names and layout can vary slightly between Mac releases.

Add a Form Control button in Excel for Windows

Open the workbook and select the worksheet where the button should appear.
Choose Developer > Insert. Under Form Controls, select Button.
Drag on the worksheet to set the button’s size. Excel opens the Assign Macro dialog after you draw it.
Select the macro you want and choose OK. If the macro does not exist yet, choose Cancel, add the code, and assign it afterward.
Right-click the button and choose Edit Text to change its caption. Click outside the control when you are finished editing.
Save the workbook as an *.xlsm file and, before running a macro that changes data, save a separate copy.

To change the assignment later, right-click the Form Control button and choose Assign Macro. A normal click runs the assigned macro, so use the right mouse button when you intend to edit rather than activate the control. You can also resize or move the button by selecting its border and dragging it.

Create a small macro and connect it

Here is a simple example. It places the current date and time in cell B1 of the active worksheet. It will replace anything already in B1, so choose a different cell or adjust the code if that cell contains important information. In Excel, open Developer > Visual Basic, choose Insert > Module in the editor, and put the procedure in that standard module:

Sub StampRunTime()
    Dim ws As Worksheet

    ' Use the currently active worksheet.
    Set ws = ActiveSheet

    ' Record the current date and time in B1.
    ws.Range("B1").Value = Now
    ws.Range("B1").NumberFormat = "yyyy-mm-dd hh:mm"
End Sub

Return to the worksheet and use Developer > Insert > Form Controls > Button to draw a button. In Assign Macro, select StampRunTime and choose OK. Change the button text to something specific, such as “Record run time,” so the action is understandable. If you instead want to assign the macro to an existing button, right-click that button and choose Assign Macro.

This procedure uses the active worksheet, meaning the sheet selected when the button is clicked. If the action must always affect a particular sheet, use an explicit worksheet reference instead and make sure the worksheet name matches the workbook. Test the button on a copy first. Macro actions generally cannot be reversed with Ctrl+Z, and the same limitation applies when a macro is launched by a button.

How the button and macro work together

A Form Control button stores a link to a macro name. When clicked, Excel looks for an eligible procedure with that name in the workbook or another available macro location. For a straightforward button assignment, put a Public Sub procedure with no required arguments in a standard module. A procedure written in a worksheet’s code module, marked Private, or requiring input arguments may not appear as an assignable macro in the expected way.

The button does not make the macro run automatically when someone opens the workbook. The user must click it, and Excel must permit the macro to run. This separation is deliberate: a visible control provides an action, while Excel’s macro security settings govern whether VBA is allowed to execute.

Form Control buttons are often a good choice for simple workbook actions because they are easy to add and assign. A worksheet shape can also be assigned to a macro: insert a shape, right-click it, and choose Assign Macro. Shapes offer more styling freedom, but they are still just a clickable object linked to code. Changing a shape’s appearance does not change what its assigned macro does.

Choose between Form Controls, shapes, and ActiveX

Form Control button: A practical default for a basic button that runs one macro. It has a caption and a direct Assign Macro option.
Shape assigned to a macro: Useful when you want a custom-looking call-to-action. The shape runs the assigned macro when clicked, but it does not provide all the behavior of a dedicated control.
ActiveX CommandButton: Offers more customization and event-driven programming on supported Windows desktop versions, but adds complexity. ActiveX controls are not supported in Excel for Mac and can be affected by security restrictions, so they are usually not the best choice for a workbook shared across platforms.

For a workbook used by a team with mixed Windows, Mac, and web users, prefer a simple Form Control or a clearly labeled shape and verify the workflow on each platform. If a macro is essential, Excel for the web is not a substitute for desktop Excel: it can open a workbook containing VBA, but it cannot create, edit, or run VBA macros.

Mac and Excel for the web differences

Excel for Mac includes VBA and supports many Form Control workflows, but the ribbon arrangement and dialog details may differ from Windows. Use the Developer tab to look for the form-control insertion options, then test the assigned macro in the actual Mac version used by your audience. ActiveX controls are Windows-only, so do not choose one for a workbook that must work on Mac.

Excel for the web does not run VBA macros or let users create and edit VBA code. A workbook may still open there, but a button that depends on a VBA macro will not provide its desktop action. If browser users need automation, consider an Office Script or a Power Automate workflow where appropriate; those are different tools with different capabilities, not VBA buttons that happen to run in a browser.

Macro security and safe sharing

Excel may block macros or show a security warning when a workbook is opened. Only enable macros when you trust the file and understand its source and purpose. Do not lower security settings broadly just to make one button work. Organizations can also apply policies that prevent users from running VBA, in which case the organization’s IT guidance takes precedence.

When sharing a workbook, tell recipients what the button does and which desktop version or platform they need. A button is not proof that the file is safe: VBA can perform actions beyond what is visible on the worksheet. For a workbook from another person, confirm its origin and review the macro before running it if you have the skills to do so. A signed macro or a trusted location may be part of an organization’s approved process, but users should follow their organization’s rules rather than changing security settings on their own.

Keep a clean backup before testing. Use a descriptive button caption, avoid silently overwriting user data, and consider adding a confirmation prompt to macros with consequential changes. Explain any required inputs in nearby worksheet text. These small choices make it less likely that someone clicks the button expecting one action and gets another.

Common button problems and fixes

The macro is missing from Assign Macro: Check that the code is in a standard module, that the procedure is a Public Sub with no required arguments, and that the workbook containing it is open. Also confirm that the procedure name has not changed.
Clicking the button does nothing: Right-click it and inspect Assign Macro. Confirm that the selected macro exists, then check whether Excel is blocking macros or organizational policy prevents them from running.
The button edits instead of running: You may be in text-edit or object-selection mode. Click away, then use a normal left-click to run it. Use a right-click when you need the editing menu.
The button works on Windows but not in a browser: Excel for the web cannot execute VBA. Open the workbook in desktop Excel or redesign the task using a web-compatible automation option.
The code runs but changes the wrong sheet: Code using ActiveSheet affects whichever worksheet is active at click time. Reference a named worksheet when the action must always target the same sheet.
The macro is gone after saving: Check whether the workbook was saved as *.xlsx. Save a working copy as *.xlsm and make sure to preserve the macro-enabled format.

If a control is difficult to select because it overlaps cells or other objects, right-click its edge to open its menu. For layout behavior, right-click the control, choose Format Control, and inspect the Properties options. These options determine whether the object moves or resizes with cells; they do not alter the macro itself.

Frequently Asked Questions

How do I assign a macro to a button in Excel?
Right-click a Form Control button, choose Assign Macro, select the procedure, and choose OK. For a new button, Excel presents the Assign Macro dialog after you draw it on the worksheet.

Can I add a VBA button without the Developer tab?
You can assign a macro to a worksheet shape, but the usual Form Control insertion commands are on the Developer tab. Enable that tab in Excel’s ribbon settings, or use a shape from the Insert tab and assign its macro from the right-click menu.

Why does Excel say the macro cannot be run?
The macro may be blocked, renamed, missing, or unavailable in the current workbook. Check the file format, security warning, macro assignment, and whether the procedure is an eligible public macro in a standard module.

Can I use a VBA button in Excel for the web?
No. Excel for the web can open workbooks that contain VBA, but it cannot run, create, or edit VBA macros. Use desktop Excel for the button’s VBA action.

Can a VBA button action be undone?
Usually not with Ctrl+Z. Excel’s Undo history does not normally reverse changes made by VBA, so save a copy before testing a macro that edits or deletes workbook data.

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.