Creating Custom Toolbars and Menus
You might feel perfectly comfortable running a macro by pressing a shortcut key combination or even pressing F5 in the Microsoft Visual Basic Editor. But if you're going to give a macro to somebody else, you want to make it as easy to run as possible. One way to make a macro easy to run is to integrate it into the Excel environment. You can initiate most built-in commands by choosing a menu command or clicking a toolbar button. By adding your macros to menus and toolbars, you can make your macros seem as if they're integral parts of Excel.
Try the ZoomIn and ZoomOut Macros
The Chapter09 workbook already contains two simple macros, ZoomIn and ZoomOut. They are macros that you'll assign to custom toolbar buttons and menu commands. Here are the macros:
Sub ZoomIn() Dim myZoom As Integer myZoom = ActiveWindow.Zoom + 10 If myZoom <= 400 Then ActiveWindow.Zoom = myZoom End If End Sub Sub ZoomOut() Dim myZoom As Integer myZoom = ActiveWindow.Zoom - 10 If myZoom >= 10 Then ActiveWindow.Zoom = myZoom End If End Sub
Each macro considers a new value for the Zoom property of the active window. If the new value is within the acceptable limits (between 10% and 400%, the range of zoom factors in Excel), it changes the property; otherwise it does nothing.
In the Visual Basic toolbar, click the Run Macro button. Select ZoomIn and click Edit.
Press F5 a few times to see how the worksheet zooms in. Step through the macro if you want, to become comfortable with how it works.
Go to the ZoomOut macro. Press F5 as necessary to return the window to the normal zoom level.
These are 'typical' macros. In this chapter, you'll learn new ways to run them.
Create a Custom Toolbar
A toolbar button is a convenient way to launch a macro. A toolbar is small and easy to show or hide. An icon can make a toolbar button easy to find, and a ToolTip can make an icon easy to remember.
Activate the Excel window, right-click any toolbar, and click the Customize command.
In the Customize dialog box, click the Toolbars tab.
Click the New button, type Zoom as the name for the toolbar, and then click OK.
A new, empty toolbar appears. The Customize dialog box is still open and available to help you fill the toolbar.
Click the Commands tab in the Customize dialog box, and select Macros from the Categories list.
Drag the Custom Button item (complete with its happy face) from the Commands list onto the Zoom toolbar.
When you drag the item onto the toolbar, the Modify Selection button becomes available in the Customize dialog box.
Click the Modify Selection button, press N to select the Name box, and type Zoom &In as the new name. Don't press the Enter key.
The value in the Name box determines what the ToolTip for the toolbar button will display. The ampersand (&) precedes the letter that will be underlined if you use this command on a menu. The ampersand has no effect on the toolbar button, but put it there anyway.
Click the Change Button Image command, and click the Up Arrow icon.
The icon on the button changes, and the menu disappears.
|Tip || |
If the menu disappeared before you could click the Change Button Image command, click the Modify Selection button again to redisplay the menu.
Click the Modify Selection button, and click the Assign Macro command. Select the ZoomIn macro, and then click OK.
Repeat steps 4 through 8, but this time give the button the name Zoom &Out, select the Down Arrow icon, and assign the ZoomOut macro to the button.
Click the Close button in the Customize dialog box, and then try the toolbar buttons. Hold the mouse over the button to see the ToolTip appear. Hide and redisplay the toolbar.
Once you have a macro, it's easy to assign it to a toolbar button. Use the Customize dialog box to create and add buttons to the toolbar. Use the Modify Selection button to change the name, icon, and macro for a button.
Create a Custom Menu
A menu command is another convenient way to launch a macro. A menu command stays out of the way, reducing clutter on the desktop. The menu it belongs to, however, is always available, whereas a toolbar can be temporarily hidden.
In Microsoft Office XP, a menu is really just a specialized toolbar, which means that adding a command to a menu is just as easy as adding a command to a toolbar.
Right-click any toolbar, and click the Customize command to display the Customize dialog box.
If you don't see the Zoom toolbar, try moving the Customize dialog box out of the way. Click the Commands tab, and select New Menu from the Categories list.
The New Menu category has only a single item: New Menu. You can use this item to create a new menu on a menu bar or a new submenu on an existing menu. You can even add a menu to a toolbar.
Drag the New Menu item up to the Excel menu bar, dropping it between the Window and Help menus.
Click the Modify Selection button, change the value in the Name box to &Zoom, and press the Enter key.
If you look closely at the Zoom menu, you can see that the letter Z is underlined. That's because there's an ampersand (&) in front of the Z in the menu item name. The underlined letter, which lets you execute the command by pressing the Alt key followed by that letter, is called an access key or an accelerator key.
You could add brand new commands to the Zoom menu, but since you already have the toolbar buttons on the Zoom toolbar, you can copy them to the Zoom menu. (You should put commands on both a toolbar and a menu bar, giving a user the choice of which to use.)
Drag the Zoom In toolbar button (the one with the Up Arrow icon) up to the Zoom menu. A small, blank menu appears. Drag the button onto the menu, and then press and hold the Ctrl key as you release the mouse button.
|Tip || |
When copying a toolbar button, be careful not to release the mouse button too soon. If you don't press and hold the Ctrl key when you release the mouse button, you'll move the toolbar button rather than make a copy. If you do accidentally move the button, simply press and hold the Ctrl key as you drag it back from the menu to the toolbar.
The command name for the new menu item is the same as the ToolTip for the toolbar button. The command name has the letter I underlined because you had the foresight to add the ampersand when you created the button in the 'Create a Custom Toolbar' section earlier in this chapter. When you add a name to a toolbar button, insert an ampersand in front of the letter that you would want underlined if you were to move the button to a menu.
Drag the Zoom Out toolbar button up to the Zoom menu, and press and hold the Ctrl key as you release the mouse.
Close the Customize dialog box, and try the menu commands. Test them using the keyboard shortcuts. Press Alt, Z, I, and Alt, Z, O to make sure the accelerator keys work properly.
When you add a command to a toolbar, you can easily copy it to a menu. Or if you add a command to a menu, you can easily copy it to a toolbar. The general term that includes both toolbars and menus is command bar. The Customize dialog box for command bars makes moving between toolbars and menus delightfully simple.
Run Macros from a Closed Workbook
Normally, you can run a macro only when the workbook that contains the macro is open. Toolbar buttons and menu commands, however, have a unique capability: they remember where to find a macro, even when that macro's workbook is closed.
Save and close the Chapter09 workbook. Click the New button to create a new workbook.
Click the Zoom In toolbar button on the Zoom toolbar.
The Chapter09 workbook automatically opens and hides behind the active workbook, and the macro runs. Depending on your macro security setting and whether you have signed the workbook, you might be prompted for permission to enable macros.
Close the temporary workbook that you created.
This will reveal the Chapter09 workbook.
Remove Menus and Toolbars
Toolbar buttons and menu commands customize a user's workspace. Once you create a custom menu or toolbar, it remains a part of Excel on that computer until you remove it. Since you probably won't be using the Zoom toolbar or menu on an ongoing basis, you should remove them now. Here's how:
Right-click any toolbar, and click Customize to show the Customize dialog box.
Click the Toolbars tab, select the Zoom toolbar in the list, click the Delete button, and then click OK.
Click the Zoom menu, press and hold the mouse button, and drag the menu off the menu bar and onto the Excel worksheet.
The menu permanently disappears.
Click the Close button in the Customize dialog box.
Toolbar buttons and menu commands are effective tools for customizing a user's copy of Excel. They remain available and active even when you close the workbook containing the macros they're attached to. Sometimes, however, you might want to give someone a button that's available only when a specific workbook is open. A command button works like that.