A CommandBar object such as a toolbar contains CommandBarControl objects. These objects are accessed via the Controls property of the CommandBar object. Three types of controls are available:
♦ Button:A CommandBarButton object
♦ ComboBox:A CommandBarComboBox object
♦ Menu:A CommandBarPopup object
The following Test procedure displays the Caption property for the first CommandBarControl object contained in the Standard toolbar which has an index of 3.
MsgBox CommandBars(3).Controls(1).Caption End Sub
When you execute this procedure, you'll see the message box shown in Figure 22-7 (assuming that your Standard toolbar has not been modified).
Using index numbers for command bar controls works regardless of the user's setting of the Always Show Full Menus option (located in the Options tab of the Customize dialog box).
Figure 22-7: Displaying the Caption property for a control.
Rather than use an index number to refer to a control, you can use its Caption property setting. The following procedure produces the same result as the previous one:
MsgBox CommandBars("Standard").Controls("New").Caption End Sub
Referring to a control by using its caption is language-dependent.Therefore, the example above will not work in non-English language versions of Excel. The solution is to use the FindControl method to locate the control by using its Id property. I describe this in Chapter 23.
If you display the Caption property for a control, you'll see that it probably includes an ampersand (&). The letter following the ampersand is the underlined hot key in the displayed text (for example, &New). When you refer to a command bar control by using its Caption property, there is no need to include the ampersand.
In some cases, Control objects may contain other Control objects. For example, the first control on the Drawing toolbar contains other controls. (This also demonstrates that you can include menu items on a toolbar.) The concept of Controls within Controls will become clearer in Chapter 23, where I discuss menus.
Was this article helpful?