Toolbars Menu Bars and Popups

Figure 15-1 shows the standard Worksheet menu bar at the top of the Excel window in Excel 2003.

Figure 15-1

The Worksheet menu bar contains menus, such as File and Edit. When you click a menu, you see another list containing commands and menus:

□ Cut and Copy are examples of commands in the Edit menu.

□ Clear is an example of a menu contained within the Edit menu.

Figure 15-2 shows the Standard toolbar in Excel 2003.

Figure 15-2

Toolbars contain controls that can be clicked to execute Excel commands. For example, the button with the scissors icon carries out a cut. Toolbars can also contain other types of controls, such as the zoom combo box (two from the end of the Standard toolbar in Figure 15-2) that allows you to select or type in a zoom factor, displayed as a percentage. Some toolbars contain buttons that display menus.

Figure 15-3 shows the shortcut menu that appears when you right-click a worksheet cell in Excel 2003.

This shortcut menu contains commands, such as Paste, and menus, such as Delete, for those operations appropriate to the selected context, in this case a worksheet cell.

Figure 15-3

To summarize, a command bar can be any one of three types. It can be a menu, toolbar, or shortcut popup menu. When you create a command bar using VBA, you specify which of the three types it will be, using the appropriate parameters of the Add method of the CommandBars collection. You will see examples of this later in the chapter. You can find out what type an existing command bar is by testing its Type property, which will return a numeric value equal to the value of one of the following intrinsic constants.


CommandBar Type




Menu bar

msoBarTypePopup Shortcut menu msoBarTypePopup Shortcut menu

Controls on command bars also have a Type property similar to the msoXXX constants shown in the preceding table. The control that is used most frequently has a Type property of msoControlButton, which represents a command such as the Copy command on the Edit menu of the Worksheet menu bar, or a command button on a toolbar, such as the Cut button on the Standard toolbar. This type of control runs a macro or a built-in Excel action when it is clicked.

The second most common control has a Type property of msoControlPopup. This represents a menu on a menu bar, such as the Edit menu on the Worksheet menu bar, or a menu contained in another a menu, such as the Clear submenu on the Edit menu on the Worksheet menu bar. This type of control contains its own Controls collection, to which you can add further controls.

Controls have an Id property. For built-in controls, the Id property determines the internal action carried out by the control. When you set up a custom control, you assign the name of a macro to its OnAction property to make it execute that macro when it is clicked. Custom controls have an Id property of 1.

Many built-in menu items and most built-in toolbar controls have a graphic image associated with them. The image is defined by the Faceld property. The Id and Faceld properties of built-in commands normally have the same numeric value. You can assign the built-in FaceId values to your own controls, if you know what numeric value to use. You can determine these values using VBA, as you will see in the next example.

0 0

Post a comment