Customizing menus

Another way to control the user interface in spreadsheet applications is to modify Excel's menus or to create your own menu system. Instead of creating buttons that execute macros, you can add one or more new menus or menu items to execute macros that you've already created. An advantage to custom menus is that the menu bar is always visible, whereas a button placed on a worksheet can easily scroll out of view.

Beginning with Excel 97, Microsoft implemented an entirely different way of dealing with menus. As you'll see in Chapter 22,a menu bar is actually a toolbar in disguise. Figure 6-2 shows an example of a new menu added to Excel. This menu was created by my Power Utility Pak add-in, which is included on the companion CD. Each menu item triggers a macro.

Figure 6-2: This new menu was created by an add-in.

There are two ways to customize Excel's menus. You can use VBA code to make the menu modifications, or you can edit the menu directly by choosing the View ^ Toolbars ^ Customize command.

As I explain in Chapter 23, the best approach is usually to use VBA commands to modify the menus. You have complete control over the menus, and you can even perform such operations as disabling the menu item or adding a checkmark to the item.

Menu modifications that you make by choosing the View ^ Toolbars ^ Customize command (see Figure 6-3) are permanent. In other words, if you make a menu change (such as the removal of a menu item), that change will remain in effect even if you restart Excel.

Figure 6-3: The Customize dialog box is where you make manual changes to the Excel menu system.

The Menu Editor (which debuted in Excel 5) was removed, beginning with Excel 97. Menus that were created by using the Menu Editor will continue to function when the workbook is loaded into Excel 97 or later. However, the only way to modify or delete menus created with the Menu Editor is to use Excel 5 or seek a utility that was designed for this purpose.

You'll find that you can customize every menu that Excel displays, even the shortcut menus that appear when you right-click an object. You must use VBA to customize the shortcut menus: You can't do so manually. Figure 6-4 shows a customized shortcut menu that appears when you right-click a chart. Notice that this shortcut menu has two new commands that aren't normally available (that is, the last two menu items).

I cover custom menus in detail in Chapter 23.

I cover custom menus in detail in Chapter 23.

Figure 6-4: An example of a customized shortcut menu.

Was this article helpful?

0 0

Post a comment