Changing menu captions

You can change the text displayed (the Caption) for both custom and built-in menus and menu items. You do so by changing the Caption property. This example changes the Help menu in the Worksheet menu bar so that it displays Assistance:

Sub ChangeHelp()

CommandBars(1).Controls("Help") .Caption = "&Assistance"

End Sub

The following example, available on this book's Web site, changes the text of all the menus, menu items, and submenu items to uppercase letters — probably not something you'd want to do, but it does give Excel a new look:

Sub UpperCaseMenus() On Error Resume Next For Each Menu In CommandBars(1).Controls Menu.Caption = UCase(Menu.Caption) For Each Menultem In Menu.Controls

Menultem.Caption = UCase(MenuItem.Caption) For Each SubMenu In MenuItem.Controls

SubMenu.Caption = UCase(SubMenu.Caption) Next SubMenu Next MenuItem Next Menu End Sub

Figure 20-6 shows how the menus look after you execute this routine. To return things to normal, see the "So you messed up your menus, eh?" sidebar in this chapter.

So you messed up your menus, eh?

As you work your way through this chapter, I hope you try out various examples and write your own code to change the Excel menus. In the process, your menus might get messed up. Mine did as I was writing this chapter. Don't fret. It's easy to restore the menu bar to its factory-fresh, out-of-the box state:

1. Choose ViewOToolbarsO Customize.

The Customize dialog box appears.

2. Click the Toolbars tab.

3. Select the Worksheet Menu Bar item (or the Chart Menu Bar item if you've messed up that menu).

4. Click Reset.

Or you can just execute this VBA statement to reset the Worksheet Menu Bar:

0 0

Post a comment