Leading the way in Microsoft Office Development

Step 1 - Write or Record the Macro

Any macros that you want to be accessible from any workbook can be saved into your Personal Macro Workbook.

The Personal Macro Workbook is always open when Excel is open, meaning that the macros are available from any workbook.

If you are recording the macro make sure you change the "store macros in" drop-down box to Personal Macro Workbook.

The Personal Macro Workbook is hidden from view but can be seen in the Projects window in the Visual Basic Editor.

Alternatively you could save your macros into an Excel add-in.

For more information about Excel add-ins, please see the Add-ins section.

Step 2 - Display the Customise dialog box

Before you do that make sure that the toolbar you want to add the button to is visible.

Select (View > Toolbars > Customise) or alternatively select (Tools > Customise).

Select the Commands tab. Scroll to the bottom of the Categories list and select "Macros".

In the Commands list box, select Custom Button.

 (Tools > Customise) dialog box

Step 3 - Add the additional Toolbar button

To add an additional button to an existing toolbar drag the "Custom Button" in the Commands list box to the toolbar.

You will see a black line appear indicating where the button or command will appear if you release the mouse button.

If you are adding several custom buttons to a toolbar it is quicker to add one custom button and then to copy that using the Ctrl key.

Hold down the Ctrl key and drag and drop the button to create a copy.

Step 4 - Assign the Macro

To assign a macro to a command button press the "Modify Selection" drop-down menu and choose "Assign Macro".

Alternatively you could just right click the button and select "Assign Macro" at the bottom of the list.

This will display the "Assign Macro" dialog box which looks very similar to the "Macros" dialog box.

This dialog box displays a list of all the macros in all the open workbooks.

Select the macro you want to run and press OK.

 Shortcut menu when you right mouse click on a custom button

You can change the icon that is displayed on the toolbar by using the "Change Button Image" submenu. This is discussed later.

Step 5 - Running the Macro

Be aware that any changes made to the toolbars and menus are permanent.

This new menu item will always be displayed now when you open Excel since your toolbar and menu customisations are saved.

Make sure that any macro you assign to this menu item is contained in a workbook that will be available.

If the workbook containing your macro is not currently open when you press this command it will be opened for you automatically.

Step 6 - Things to Remember

You can remove a button from a toolbar by dragging the button from the toolbar and releasing it over the "Customise" dialog box.

You can also remove a button from a toolbar by holding down the Alt key as you drag the button. This method does not require the (View > Toolbars > Customise) dialog box to be displayed.

If you press a command button that has not been assigned a macro then the "Macro" dialog box will be displayed automatically allowing you to choose a macro to assign to that button.

For more information on how to customise your toolbars, please see the Toolbars & Menus section.