Deleting and Creating the Custom Menu

Excel's main menu bar is known in VBA as Application.CommandBars(l). This menu bar typically has popup controls named File, Edit, View, and so on. Use the Add method to add a new popup menu item to the main menu. When adding the control, you need to specify the name of the menu item and its location.

The name of the menu item should be the text that you want to appear on the menu bar. If you specify that the name is "XYZ Co," then this appears on the menu bar. However, most of Excel's menu bars have a keyboard shortcut. For example, pressing Alt+V brings up the View menu. You can tell this because the V in View is underlined. It is easy to assign a keyboard shortcut to your custom menu. Simply put an ampersand before the letter you want to use as a keyboard shortcut. Entering &XYZ Co causes the X to be underlined on the Excel menu bar.

It is important to remember that the keyboard shortcuts for D, E, F, H, I, O, T, V, W are already taken by the regular Excel menu bar. You can assign two menu items the same shortcut without causing an error, but neither shortcut will work.

By default, Excel has 10 menu items: the XL icon, then File, Edit, View, Insert, Format, Tools, Data, Window, and Help. Indicating that the new menu item should appear before the tenth menu item puts the custom menu before the Help menu. In Windows, it is possible to put your custom menu item before the eleventh item, which puts the custom menu after Help. The Macintosh people feel so adamant that Help must be the last menu item that attempting to add a custom menu to the right of Help returns an error on the Macintosh.

0 0

Post a comment