Assign a macro to a button

You can use a button (a form control) to run a macro that performs an action when a user clicks it. For example, you might use a button to automate the printing of a worksheet, the filtering of data, or the calculation of numbers.

Windows Mac



After you create a macro, you can assign it to a button you click to run the macro. You can assign a macro to a button on the Quick Access Toolbar or to a button in your own personal group on the ribbon.

If you want a macro button to be available in other workbooks, assign it to a macro that was created in a personal workbook.

Add a macro button to the Quick Access Toolbar

1. Click File > Options > Quick Access Toolbar.

2. In the Choose commands from list, click Macros.

   

3. Select the macro you want to assign a button to.

4. Click Add to move the macro to the list of buttons on the Quick Access Toolbar.

5. To replace the default macro icon with a different button for your macro, click Modify.

6. Under Symbol, select a button icon for your macro.

   

7. To use a friendlier name for the button, in the Display name box, enter the name you want.

   You can enter a space in the button name.

8. Click OK twice.

The new button appears on the Quick Access Toolbar, where you can click it to run the macro.

Tip:  When you save the workbook, buttons you assign to macros in the personal workbook will be available in every workbook you open.

Add a macro button to your own group on the ribbon

1. Click File > Options > Customize Ribbon.

2. Under Customize the Ribbon, in the Main Tabs list, check the Developer box if it is not already checked.

   

3. Pick the tab where you want to add your own group.

   For example, pick Home, to add your group to the Home tab.

4. Select New Group.

   That adds New Group (Custom) to the tab you picked.

5. To use a better name for your new group, click Rename, type the name you want in the Display name box, and then click OK.

   You can enter a space in the name. For example, type My Macros.

6. To add a macro to the group, in the Choose commands from list, click Macros.

7. Select the macro you want to add to your new group, and then click Add. The macro is added to the My Macros group.

8. To use a friendlier name, click Rename, and then type the name you want in the Display name box.

   You can enter a space in the name.

9. Under Symbol, select a button icon for your macro.

10. Click OK twice.

Your new group appears on the tab you picked, where you can click the button to run the macro.

Tip:  When you save the workbook, buttons you assign to macros in the personal workbook will be available in every workbook you open.

Excel 2016 for Mac

1. On the Developer tab, in the Controls group, click Button.

   If the Developer tab is not available

   1. Go to Excel > Preferences... > Ribbon & Toolbar.

   2. In the Customize the Ribbon section, under Main Tabs, check the Developer check box, and press OK.

2. Click the worksheet location where you want the upper-left corner of the button to appear.

3. In the Assign Macro dialog box, click the name of the macro that you want to assign to the button, and then click OK.

4. To resize the button, drag the sizing handles.

5. To specify the control properties of the button, Control+Click or right-click the button, and then click Format Control.

Excel 2011 for Mac

1. On the Developer tab, in the Forms Control group, click Button.

   If the Developer tab is not available

   1. On the right side of the ribbon, click , and then click Ribbon Preferences.

   2. Under Customize, select the Developer check box.

2. Click the worksheet location where you want the upper-left corner of the button to appear.

3. In the Assign Macro dialog box, click the name of the macro that you want to assign to the button, and then click OK.

4. To specify the control properties of the button, Control+Click or right-click the button, and then click Format Control.