menu Archives - Peltier Tech

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.