Bz

CREATE A CUSTOM MENU

Excel enables you to add custom menus to house links to VBA macros or other commonly used Excel commands. You can design a VBA procedure to create new menus that display within Excel. Typically you place most menus on the active menu bar. The active menu bar in Excel is the first object in the CommandBars collection. All menus you add become CommandBarControls on the active menu bar.

You can create a new Excel menu with the Add method associated with the CommandBarControls collection. Although the Add method has five different parameters — Type, Id, Parameter, Before, and Temporary — they are all optional. When creating a new menu, you only need to use the Type and Before parameters. You must specify a value of msoControlPopup for the Type parameter to create a new menu. You use the Before parameter to indicate where on the menu bar to place the new menu. You do this by specifying the index value of the menu in front of which you want to place the new menu.

After you create the menu, you can set several different properties for the menu. The most commonly set property is the Caption property. The Caption property contains the display value for the menu on the menu bar. If you look at Excel menus, you see that most of them have a shortcut key that displays the menu when you click Alt and the key simultaneously. Excel identifies the shortcut key on a menu by underlining the appropriate character in the menu name. You can specify the shortcut key as part of the Caption property value by placing the & in front of the appropriate character.

CREATE A CUSTOM MENU

CREATE A CUSTOM MENU

□ Create a new subroutine.

0 Type Dim ExcelMenu As CommandBarControl, replacing ExcelMenu with the variable used to cycle through the menus.

^0 Type For Each ExcelMenu In CommandBars(1).Controls.

□ Create a new subroutine.

^0 Type Dim NewMenu As CommandBarControl, replacing NewMenu with the variable for the new menu.

0 Type Dim ExcelMenu As CommandBarControl, replacing ExcelMenu with the variable used to cycle through the menus.

^0 Type For Each ExcelMenu In CommandBars(1).Controls.

CUSTOMIZING DIALOG BOXES, MENUS, AND TOOLBARS

When adding new menus to the active menu bar using the Before parameter, you need to specify an index value of an existing menu. If you do not specify a value for the Before parameter, Excel adds the new menu to the end of the active menu bar. The sample code for this section uses the For Each looping statement to count the number of menus on the active menu bar and then places the new menu before the last menu.

Another way to specify an index value involves using the FindControl method to locate the desired menu and then using the index value to specify where to place the new menu. With this method, you specify the ID setting for the menu. The example locates the Insert menu, which has an ID of 30005. The table lists the Excel built-in menus ID values.

Example:

MenuIndex = CommandBars(1).FindControl(id:=30005).Index

MENU

ID

File

30002

Edit

30003

View

30004

Insert

30005

Format

30006

Tools

30007

Data

30011

Chart

30022

Window

30009

Help

30010

° Type Set NewMenu = CommandBars(l). Controls.Add _.

before:=CommandBars(1). Controls(Count).Index).

± Switch to Excel and run the macro.

Excel creates the new menu.

° Type Set NewMenu = CommandBars(l). Controls.Add _.

—0 Type (Type:=msoControlPopup.

before:=CommandBars(1). Controls(Count).Index).

Type NewMenu.Caption :

"&Macros", replacing "&Macros" with the name for the menu.

± Switch to Excel and run the macro.

■ Excel creates the new menu.

ADD ITEMS TO A MENU

You can place macros and other Excel commands that you use frequently on menus. You can place additional menu items to both existing and custom menus by adding a new CommandButtonControl object. Because the menu to which you add the menu item is also a CommandButtonControl object, the difference between the two controls is the value of the Type parameter. The menu has a Type parameter value of msoControlPopup, and the menu item has a value of msoControlButton. See the section "Create a Custom Menu" for more information on creating menus.

You create a menu item using the Add method associated with the CommandBarControls collection. When you call the Add method, the only parameter you need to use is the Type parameter with a value of msoControlButton.

You can set several different properties with the menu item. The main properties you need to set are the Caption and the OnAction properties. The Caption property contains the display value for the menu item. You can also set the shortcut key that executes the menu item when you press the Alt key and the shortcut key simultaneously. Excel identifies the shortcut key on a menu by underlining the appropriate character in the menu item name. You can specify the shortcut key as part of the Caption property value by placing the & in front of the appropriate character.

The OnAction property specifies the macro to execute when a user clicks a menu item. To specify a macro, place the macro name in quotes. Remember that if you do not have the workbook containing the macro open when you click the menu item, Excel cannot find the specified macro. To avoid this situation, consider placing the macro in the Personal Macro Workbook. See Chapter 1 for more information on the Personal Macro Workbook.

ADD ITEMS TO A MENU

ADD ITEMS TO A MENU

□ Create a new subroutine.

'—H Type Dim UseMenu As CommandBarControl, replacing UseMenu with the menu variable.

< Type Dim NewMenuItem As CommandBarControl, replacing NewMenuItem with the menu item variable.

^Q Type Set UseMenu = CommandBars(l). Controls("Macros"), replacing "Macros" with the name of the menu.

S Type Set NewMenuItem = UseMenu.Controls.Add(Type: =msoControlButton).

□ Create a new subroutine.

'—H Type Dim UseMenu As CommandBarControl, replacing UseMenu with the menu variable.

< Type Dim NewMenuItem As CommandBarControl, replacing NewMenuItem with the menu item variable.

^Q Type Set UseMenu = CommandBars(l). Controls("Macros"), replacing "Macros" with the name of the menu.

S Type Set NewMenuItem = UseMenu.Controls.Add(Type: =msoControlButton).

CUSTOMIZING DIALOG BOXES, MENUS, AND TOOLBARS

You can create submenus on a menu to organize commands. For example, you can create a Macro menu to group common macros together. You create submenus by adding a new CommandBarControl object with the type msoControlPopup to the main menu. You can then add new menu items to the submenu using code similar to that dealing with the SubMenuItem object.

Q Type With NewMenuItem.

"MySub", replacing "MySub" with the name of the macro to execute.

, Switch to Excel and run the macro.

■ Excel adds the menu item to the specified menu.

Q Type With NewMenuItem.

—□ Type .Caption = "Message &Box", replacing "Message &Box" with the caption for the menu item.

"MySub", replacing "MySub" with the name of the macro to execute.

, Switch to Excel and run the macro.

■ Excel adds the menu item to the specified menu.

0 0

Post a comment