To assign a macro to a form control button in Excel, right-click the button, select Assign Macro, choose the macro from the list, and click OK. This works for both Form Controls (from the Developer tab) and ActiveX buttons, though the steps differ slightly for ActiveX (you double-click the button in design mode and write code in the VBA editor). Below, we cover the full process, how to choose between control types, common pitfalls, and answers to frequent questions.
Background: What Are Form Control Buttons?
Form controls are simple user-interface elements you can place on a worksheet, such as buttons, check boxes, list boxes, and scroll bars. They’ve been part of Excel for decades and are lightweight—they don’t add much file size or complexity. The Button control (often called a “Form Control Button”) is a rectangle you can click to run a macro. It’s the most straightforward way to make your VBA code accessible to users who might not know how to open the macro dialog.
To insert one, you need the Developer tab visible. If it’s not, go to File > Options > Customize Ribbon, check Developer in the right pane, and click OK. Then go to Developer > Insert and choose the button icon under Form Controls (the first one, labeled “Button”). Your cursor becomes a crosshair; click and drag on the worksheet to draw the button. When you release the mouse, Excel automatically opens the Assign Macro dialog.
How to Assign a Macro: Step-by-Step
Here’s the exact process for a Form Control button:
- Make sure you have a macro already in the workbook (or in your Personal Macro Workbook). You can create one by pressing Alt+F11 to open the VBA editor, inserting a module, and typing a simple procedure like
Sub MyMacro()…End Sub. - Right-click the button on the sheet. A context menu appears with Assign Macro at the top.
- In the Assign Macro dialog, you’ll see a list of available macros (those in the current workbook and add-ins). Select the one you want.
- Click OK. The button is now linked to that macro.
If you need to change the macro later, right-click again and choose Assign Macro. To resize or move the button, hold Ctrl and click it, or right-click and then drag—this avoids triggering the macro while editing.
For an ActiveX button (the one with a “CommandButton” label), the process is different: you must be in Design Mode (Developer > Design Mode), then double-click the button to open the VBA editor and write code in the button’s Click event. This is more flexible but requires manual coding. Most users prefer Form Controls for simple tasks.
Choosing Between Form Control and ActiveX Buttons
Both button types can run macros, but they have distinct trade-offs:
- Form Control Button – Easier to set up, no code writing needed for assignment. It’s compatible with all Excel versions and works on Mac and Windows. However, it has limited formatting options (you can change font and fill color, but not much else).
- ActiveX CommandButton – Offers more properties (e.g., BackColor, Font, Enabled) and events (like MouseMove). It’s more powerful, but it requires the Developer tab and can be finicky on Mac (some properties don’t work). Also, if you share the workbook, ActiveX controls may be disabled by security settings.
For most users, the Form Control button is the right choice. It’s stable, simple, and does exactly what you need: run a macro on click.
Common Problems and How to Fix Them
Here are frequent issues people encounter:
- Button does nothing when clicked. Check that the macro name is spelled correctly in the Assign Macro dialog. Also, ensure the macro is in a standard module, not in a sheet module (unless it’s a sheet event). If the macro is in the Personal Macro Workbook, it must be visible (unhide it in the VBA project).
- “Cannot run the macro” error. This often happens if the macro is in a different workbook that isn’t open, or if the macro is disabled by security settings. Go to File > Options > Trust Center > Trust Center Settings > Macro Settings and enable “Disable VBA macros with notification” or “Enable all macros” (temporarily).
- Button moves or resizes when you click it. That’s because you’re in design mode or you’re right-clicking. To run the macro, just left-click normally. To edit, hold Ctrl and click.
- Macro works but you want to change the button text. Right-click the button, select Edit Text, and type a new label. You can also change font size and color via Format Control.
Editing the Button’s Appearance and Behavior
You can customize a Form Control button without touching code. Right-click the button and choose Format Control. In the dialog, you can:
- Set the font, size, and style.
- Change the fill color and line color.
- Adjust the size and position (absolute or relative to cells).
- Define the macro again under the “Control” tab.
Also, you can assign a keyboard shortcut to the macro itself: in the Assign Macro dialog, click Options and type a letter (e.g., “m” for Ctrl+Shift+M). This lets users run the macro without clicking the button.
VBA Code Example and Best Practices
Here’s a simple macro you might assign to a button:
Sub ShowMessage()
MsgBox
Related
- Excel Spreadsheet for Contact Information: How to Build One
- Excel Spreadsheet Attendance Template: How to Build One
- Excel Spreadsheet Calendar Template: How to Make One
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA UserForm: How to Show It in ExcelTo show a VBA UserForm in Excel, call its name followed by .Show, such as UserForm1.Show. Add vbModeless…
- Excel VBA Sort A to Z: Code, Options, and Common FixesTo sort a list A to Z with Excel VBA, sort the entire data range by its chosen…
- Excel VBA: Find All Cells With a ValueTo find every cell that matches a particular value in Excel VBA, use the Range.Find method and repeat…
- Excel VBA: Get a Range’s Length in Cells or CharactersIn Excel VBA, use Range.CountLarge to get how many cells a range contains; use Range.Rows.Count and Range.Columns.Count for…
- Excel VBA Maximum String Length: Limits and WorkaroundsA variable-length VBA String can hold approximately 2 billion characters in theory, but that is not a practical…
- VBA Dictionary: How It Works, Examples, and Common UsesA VBA Dictionary is a key–value collection: you store a value under a unique key, then retrieve it…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.