In the last couple of posts, I’ve discussed assigning macros to Forms toolbar controls and shapes, and to ActiveX controls from the Control Toolbox. Macros can also be run from a toolbar or menu.

This technique is valid for Excel 97 through 2003. The new user interface in Excel 2007 has no toolbars or menus, but a new ribbon. Adding a custom button to run a macro from this new interface will be covered in a future post.

We’ll use this simple macro for this example.

Sub HelloWorld()
  MsgBox "Hello, World! ", vbExclamation
End Sub
 

When it is run, we see a simple message.

Menu and Toolbar Buttons

If you want your macro to be available to more than one sheet, you could keep adding buttons to each worksheet. But this requires the button to have been installed. If you put the button on a toolbar or menu, you could install it once and use it whenever you wanted. It’s time to customizze the user interface. There are several ways to get started.

• Tools menu > Customize > Toolbars tab
• View menu > Toolbars > Customize > Toolbars tab
• Right click in the menu and toolbar area > Customize > Toolbars tab

Check the box in front of a toolbar’s name to make it visible, so you can add a button, or click New to create a new custom toolbar. Type a name for the new toolbar.

The toolbar appears with no buttons, and it’s not wide enough to display its whole name.

Now click on the Commands tab of the Customize dialog. Through this dialog you could add any built-in button, even many that don’t even appear in the default interface, to any menu or toolbar. That’s nice to know, but we’ll use the same technique to add a custom button for our macro. Scroll down to the Macros category in the left listbox.

There are two commands listed. They are essentially the same, just differently preformatted: the custom menu item appears as a labeled button with no icon, while the custom button appears as an icon with no label. Drag the custom button right onto your toolbar.

Right click on the button to change its name (to Hello World), adjust the display style (to image and text), and to assign a macro to the button.

While the Customize dialog is open, you can click on a menu to open it, then drag a button onto the menu.

Related Posts:

• How To: Assign a Macro to a Button or Shape
• My Customized Excel Toolbars
• How To: Record Your Own Macro
• How To: Use Someone Else’s Macro
• How To: Assign a Macro to an ActiveX Control
• AutoFilter Tricks
• Build an Excel Add-In 6 – Interface for 2003
• Installing an Add-In in Excel 2007
• Improved Macro Security Warning in Excel 2010
• My First Look at Excel 2010