Excel Tip: Assign a Macro to a Button on Your Toolbar

If you've created a macro that you plan to use frequently, you can save time by making a button for that macro and adding the button to your Excel toolbar.

Follow these steps and you'll be clicking a macro button in no time:

    1. Choose Tools | Customize, from the menus. The Customize window will appear. Click the Commands tab if it is not already selected.

    2. Click Macros from the Category list at the left. Two options will appear in the Commands section at the right: Custom Menu Item and Custom Button (you may not have the Custom Button option, depending on which version of Excel you have - no matter).

    3. Hold down your mouse button and drag the Custom Button (or Custom Menu Item if that is all you have) right out of the Customize window and onto your toolbar, in the place where you want the button to appear. Release your mouse button to drop the item onto your toolbar.

    4. Click the Modify Selection option in the Customize window. A menu will appear.

    5. Click the Assign Macro option and choose a macro from the Assign Macro window that appears. You should always choose a macro that resides in the Personal Workbook for placement on a toolbar - that way the macro will be available in every workbook. If it is not already selected, select the Personal Workbook from the Macros in option in the Assign Macro window. Click OK when you have highlighted a macro.

    6. If you would like to change the button image to a different picture, click the Modify Selection option again and choose to Edit or Change button image. Choosing Edit lets you design your own image, choosing Change produces a gallery of images from which you may select.

    Note: If your macro button is a text button instead of a figure button, select Default Style from the Modify Selection menu to change to style to a figure, then choose Edit or Change to customize the image on the button.

    7. Click Close when you are finished with the Customize window. Henceforth your new button will activate the macro with which it is associated.

    View more helpful tips!

You may like these other stories...

Earlier this week I presented the Chart Edition of AccountingWEB’s High Impact Excel webinar series. One of the many topics I covered was the Sparklines feature, which was first introduced in Excel 2010. Several...
On January 30, I led a free, one-hour webinar, High Impact Excel: Pivot Table Edition. If you missed the presentation, it’s too late to get CPE credit, but you can watch an on-demand recording. After the webinar, I...
By David Ringstrom, CPA In Part 1 of this series I showed how to use a custom number format to conditionally display decimal places. Although the technique is simple, the downside is it may not work in every situation....

Already a member? log in here.

Upcoming CPE Webinars

Sep 24
In this jam-packed presentation Excel expert David Ringstrom, CPA will give you a crash-course in creating spreadsheet-based dashboards. A dashboard condenses large amounts of data into a compact space, yet enables the end user to easily drill down into details when warranted.
Sep 30
This webcast will include discussions of important issues in SSARS No. 19 and the current status of proposed changes by the Accounting and Review Services Committee in these statements.
Oct 21
Kristen Rampe will share how to speak and write more effectively by understanding your own and your audience's communication style.
Oct 23
Amber Setter will show the value of leadership assessments as tools for individual and organizational leadership development initiatives.