How to Run a Macro with a Button or Keyboard Shortcut

How to run a macro with a button or keyboard shortcut

To run a macro from a button, go to File > Options > Quick Access Toolbar, choose Macros in the commands list, and add yours. To run it from the keyboard, open Alt + F8, select the macro, click Options, and assign a letter.

Both take about a minute. The decision worth making first is where the trigger should live, because the three options have very different reach.

TL;DR: Key takeaways

• Quick Access Toolbar button: available in every workbook, if the macro lives in your personal workbook.
• Sheet button: travels with the file, works for anyone who opens it.
• Keyboard shortcut: works only while the workbook holding the macro is open.
• Use Ctrl + Shift + a letter. A plain Ctrl + letter hijacks an Excel shortcut you already use.
• Never assign Ctrl + Z. You lose Undo for as long as that workbook is open.

Decide where the trigger should live

This is the part most guides skip, and it decides which method you want.

A button on a worksheet is part of the file. Send the workbook to a colleague and the button goes with it. It only exists on that sheet.

A Quick Access Toolbar button is part of your Excel, not the file. It follows you between workbooks but nobody else sees it.

A keyboard shortcut is attached to the macro, so it works only while the workbook containing that macro is open. There is a fourth option with no trigger at all: running the macro automatically when the workbook opens.

Assign a keyboard shortcut

1. Press Alt + F8 on Windows or Option + F8 on Mac to open the Macros dialog.
2. Select the macro and click Options.
3. Type a letter in the Shortcut key box.
4. Add a description if you want one, then click OK and close the dialog.

Choose the letter carefully. Microsoft warns that a macro shortcut "will override any equivalent default Excel shortcut key while the workbook that contains the macro is open." Assign Ctrl + Z and you lose Undo.

The fix is to hold Shift. A capital letter gives you Ctrl + Shift + Z, which Excel does not already use. On Mac the equivalent is Option + Command + letter, though Ctrl + letter works as well.

Add a button to the Quick Access Toolbar

This is the one worth setting up for a macro you run constantly.

1. Select File > Options > Quick Access Toolbar.
2. In Choose commands from, select Macros.
3. Select your macro and click Add.
4. Click Modify to pick an icon and set a friendlier display name.
5. Click OK twice.

The button appears near the ribbon and runs the macro on one click. The same route under Customize Ribbon builds your own named group, such as My Macros, on any tab.

One condition attaches to both. Microsoft is explicit: "buttons you assign to macros in the personal workbook are available in every workbook you open." Point a button at a macro stored in one workbook and it breaks the moment you work somewhere else. Keep the macro in your Personal Macro Workbook and it works everywhere.

Put a button on the sheet itself

Use this when the macro belongs to a particular workbook and other people will use it.

1. Insert a shape, or a Button form control from the Developer tab.
2. Right-click it and choose Assign Macro.
3. Pick the macro and click OK.

Clicking the shape runs the macro. Because it is drawn on the sheet, it saves with the workbook and works for whoever opens the file, provided they allow macros to run. Microsoft treats the form-control button as the standard approach here.

Why your macro shortcut stops working

The usual complaint is that a macro shortcut works one day and does nothing the next. It is almost never a bug.

The shortcut belongs to the macro, and the macro lives in a workbook. Close that workbook and the shortcut has nothing to point at. Reopen the file and it works again.

The Personal Macro Workbook solves exactly this. It opens hidden every time Excel starts, so a macro stored there is always loaded and its shortcut is always live.

Frequently asked questions

How do I assign a keyboard shortcut to an Excel macro?

Press Alt + F8, select the macro, click Options, and type a letter in the Shortcut key box. Use a capital letter so the shortcut becomes Ctrl + Shift + letter, which avoids overriding an Excel shortcut you already rely on.

Why did my macro keyboard shortcut stop working?

Because the workbook holding the macro is closed. The shortcut only works while that file is open. Store the macro in your Personal Macro Workbook, which loads hidden at startup, and the shortcut stays available in every session.

Can I add a macro button to the ribbon?

Yes. Go to File > Options > Customize Ribbon, create a New Group on any tab, choose Macros in the commands list, and add yours. Rename the group and pick an icon. It behaves the same as a Quick Access Toolbar button.

Will my macro button work for other people?

Only if the button is drawn on the worksheet. Toolbar and ribbon buttons belong to your Excel installation, not the file, so they do not travel. A shape or form control with an assigned macro saves inside the workbook and works for anyone who opens it.

Which shortcut letters are safe to use?

Anything with Shift held down, since Ctrl + Shift + letter combinations are mostly unused by Excel. Avoid plain Ctrl + C, V, X, Z, S and P, which are all in constant use. Excel does not warn you about a clash, so check before committing.

What to do next

Pick the method that matches how far the macro needs to reach: a sheet button to share it, a toolbar button for yourself, a shortcut for speed. If the macro is not in Excel yet, import it from a .bas file or record one first. For ready-made macros, the free tools library covers the common cleanup jobs.