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.
Ian says
Let me know when you do the future post ‘cos I can’t work out an easy nonprogramming way to do it at the moment!
At the same time it would be useful to know how to edit what macro is attached to a ribbon too as that is also escaping me.
Whit says
I used “Add-Ins” and installed my essbase toolbar on Excel 2007. However, none of the tools work! Do I have to associate functionality to the tools?
—-Whit—-
Jon Peltier says
I don’t know anything about Essbase. Many large third-party utilities require more than just the installation of a simple add-in. They generally have a number of helper files that also need to be correctly dealt with. Usually there is a setup file (*.exe) which does all the heavy lifting for you.
MikeB says
I would really like to know how to assign text to a macro button as can be done in Office 2003. Can’t stand the limited icon selection of 2007 and long hover delay for the macro name to display…even a way to reduce the macro name delay time would be helpful. TewakUI doesn’t reduce the dwell time. Thanks in advance for your posts! Mike…
Jon Peltier says
Mike –
If it’s a Forms button, you should be able to select the button, then select the text, then type. If it’s a Controls Toolbox button, you should be able to go to design mode, right click on the button, and choose the Edit Text command.