Changing Excel menu elements

If you get bored with Excel's standard menu text, you can change it to something else — for instance, you can change the Tools menu to the Miscellaneous menu. You can also assign your own macros to built-in menu items. You have many other options for changing menu elements, including rearranging the order of the menus on a menu bar (for example, to make the Help menu appear first instead of last).

Moving Up from Excel 5/95?

If you've customized menus in Excel 5 or Excel 95, you can pretty much forget everything that you ever learned. Beginning with Excel 97, menu customization has changed significantly in the following respects:

♦ A menu bar is actually a toolbar in disguise. If you don't believe me, grab the vertical bars at the very left of the menu bar and drag the bar away. You'll end up with a floating toolbar. The official (VBA) term for both menus and toolbars is command bar.

♦ The Excel 5/95 Menu Editor is gone. To edit a menu manually, you choose the View ^ Toolbars ^ Customize command. Understand, however, that Excel 5/95 workbooks that contain menus customized by using the old Menu Editor still work in Excel 97 and later. However, to make any changes to these modified menus, you must do so in Excel 5/95. Better yet, just remove the menu edits (using Excel 5/95) if you plan to use your workbook with a later version of Excel.

♦ There is no direct way to assign a VBA macro to a new menu item on the Tools menu. This was a piece of cake with Excel 5/95. Later in this chapter, however, I provide VBA code that you can use to add a new menu item to the Tools menu.

Excel 2000 and later, by default, displays only the most recently used menu items. In my opinion, this is one of the worst ideas that Microsoft has come up with. I can't imagine why anyone would want the order of his or her menu items to be shifting around. Fortunately, this feature can be disabled in the Options tab of the Customize dialog box.

Be careful if you change the captions for Excel's menus.Some Excel developers rely on the standard menu captions when they create new menus — and their code will fail if you've modified your menu captions. As you'll see in the later section,"Adding a menu:Take 2," using the FindControl method in your code will eliminate these problems.

The remainder of this chapter focuses on writing VBA code to modify menus.

Chapter 22 provides background information about the Customize dialog box.

0 0

Post a comment