Macro On A Menu

Removed in 2007

Write or Record the Macro

Any macros that you want to be accessible from any workbook should be saved into your Personal Macro Workbook.
The Personal Macro Workbook is always open when Excel is open meaning that the macros are available when any workbook is open.
If you are recording the macro make sure you change the "store macros in" drop-down box to Personal Macro Workbook.
This 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 addin.
For more information about Excel add-ins, please see the Add-ins section.

Display the Customise dialog box

Having your macros accessible from existing drop-down menus can make them extremely easy to find.
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 Menu Item.
(Tools > Customise) dialog box

Add the additional Menu button

To add an additional button to an existing menu drag the "Custom Button" in the Commands list box to the drop-down menu.
Dragging the button to the top of an existing drop-down menu will expand the menu and allow you to place the command button anywhere on the menu.
You will see a black line appear indicating where the button or command will appear if you release the mouse button.
It is also possible to add command buttons to existing submenus, just drag the button over the submenu triangle.

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 menu item

It is important to give your command button a name as this will be displayed on the menu.
You can prefix any of the characters with an ampersand character (&) to indicate a shortcut key.
You can change the icon that is displayed on the menu by using the "Change Button Image" submenu. This is discussed later.

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.

Important

You can remove a button from a drop-down menu by dragging the button from the menu and releasing it over the "Customise" dialog box.
You can also remove a button from a menu 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.