Excel 2010 Quick Access Toolbar Macro Button



You can add an icon to the Quick Access toolbar to run your macro. If your macro is stored in the Personal Macro Workbook, you can have the button permanently displayed in the Quick Access toolbar. If the macro is stored in the current workbook, you can change it so the icon only appears when the workbook is open.

 

Add a macro button to the Quick Access toolbar:

  1. Click File menu and select Excel Options to prompt the Excel Options dialog.

  2. In the Excel Options dialog select the Customize category from the left-side navigation.

  3. If your macro is only available when the current workbook is open, open the upper-right drop-down and change For All Documents (Default) to For <FileName.xlsm>. Any icons connected with the current workbook are displayed at the end of the Quick Access toolbar.

  4. Open the upper-left drop-down and select Macros from the list (fourth in the list). Excel displays a list of available macros in the left list box.

  5. Choose a macro from the left list box. Click the Add button in the center of the dialog. Excel moves the macro to the right list box. Excel uses a generic VBA icon for all macros. Change the icon by following steps 6 through 8.

  6. Click the macro in the right list box. Click Modify button at the bottom of the right list box. Excel displays a list of 180 possible icons.

  7. Choose an icon from the list. In the Display Name box, replace macro name with a short name that will appear in the ToolTip for the icon.

  8. Click OK to close the Modify Button dialog.

  9. Click OK to close Excel options. The new button appears on the Quick Access toolbar.



Add a macro button to the Ribbon:

  1. Click the File menu and select Excel Options to open the Excel Options dialog.

  2. In the Excel Options dialog, select the Customize Ribbon category from the left-side navigation.

  3. In the list box on the right, choose the tab name where you want to add an icon.

  4. Click the New Group button below the right list box. Excel adds a new entry called New Group (Custom) to the end of the groups in that ribbon tab.

  5. To move the group to the left in the ribbon tab, click the up-arrow icon on the right side of the dialog several times.

  6. To rename the group, click the Rename button. Type a new name, such as Report Macros. Click OK. Excel will show the group in the list box as Report Macros.

  7. Open the upper-left drop-down and choose Macros from the list. Macros category is fourth in the list. Excel displays a list of available macros in the left list box.

  8. Choose a macro from the left list box. In the center of the dialog, click Add button. Excel moves the macro to the right list box in the selected group. Excel uses a generic VBA icon for all macros. Change the icon by following steps 9 and 10.

  9. Click the macro in the right list box. Click Rename button at the bottom of the right list box. Excel displays a list of 180 possible icons. Choose an icon. Type a friendly label for the icon, such as Format Report.

  10. Click OK to close Excel options. The new button appears on the selected Ribbon tab.