Working with Command Bar Controls

At this point, your custom command bars aren't particularly useful because they can't do much of anything. In other words, they don't contain any controls that the user can click or otherwise execute. This section solves that problem by giving you the lowdown on VBA's command bar controls and by showing you how to add and modify custom command bar controls.

VBA divides command bar controls into three categories:

CommandBarButton ” This is an object that the user clicks to execute a command or run a procedure. Menu commands and toolbar buttons are examples of CommandBarButton objects.

CommandBarPopup ” This is an object that the user clicks to display a menu of items. Examples of CommandBarPopup objects are menu bar commands and menu commands that display submenus.

CommandBarComboBox ” This object takes one of three forms: a text box into which the user enters text (for example, the Name text box in the Modify Selection menu shown in Figure 14.2); a drop-down list from which the user selects an item; or a combo box that combines a text box and a drop-down list (for example, the Font and Font Size controls on the Formatting toolbar in Word and Excel).

Specifying a Control

As you learned earlier, each CommandBar object has a Controls property that returns the collection of all the controls on the command bar. You use this collection to specify individual controls using their index number, where Controls(1) is the first control on the command bar, Controls(2) is the second control, and so on. In each case, a CommandBarControl object is returned.

Another way to specify a control is to use the CommandBar object's FindControl method:

  CommandBar  .FindControl(  Type, Id, Tag, Visible, Recursive  )

`CommandBar`	The `CommandBar` object in which you want to search.
`Type`	(optional) A constant that specifies the type of `CommandBarControl` object you want to find. For custom controls, use one of the following constants that correspond to the control types discussed above: `msoControlButton` , `msoControlPopup` , `msoControlEdit` , `msoControlDropdown` , or `msoControlComboBox` . For built-in controls, Office also defines quite a number of other constants. To see these constants, look up the `Type` property of the `CommandBarControl` object in the Office VBA Help system.
`Id`	(optional) This is a unique identifier that the application supplies for each control. This identifier is returned by the `CommandBarControl` object's `Id` property.
`Tag`	(optional) Specifies the `Tag` property of the control you want to find.
`Visible`	(optional) Use True to search only for controls that are visible; use False to search for hidden controls as well (this is the default).
`Recursive`	(optional) Use True to search not only the command bar, but all of its submenus and pop-up menus ; use False to search only the command bar (this is the default).

If FindControl is successful, it returns a CommandBarControl object for the first control that matches your search criteria. If the search fails, FindControl returns the value Nothing.

The FindControl method is most often used with the Id parameter. For example, the identifier for Excel's Tools menu is 30007. So, as shown in the following statements, you can specify the Tools menu object by running FindControl with Id set to 30007:

 Dim menuTools As CommandBarControl Set menuTools = Application.CommandBars.FindControl(Id:=30007)

How do you know which identifier to use? That's tricky, but I have a solution. See "The Command Bar Info Utility," later in this chapter.

Adding a Control to a Command Bar

When customizing command bars, you have a number of different routes to take:

You can modify a built-in command bar by adding built-in controls.
You can modify a built-in command bar by adding custom controls that execute your VBA procedures.
You can modify a custom command bar by adding built-in controls.
You can modify a custom command bar by adding custom controls.

Whichever route you take, you insert a control into a command bar by using the Controls object's Add method:

  CommandBar  .Controls.Add(  Type, Id, Parameter, Before, Temporary  )

`CommandBar`	The `CommandBar` object into which you want to insert the control.
`Type`	(optional) A constant that determines the type of custom control to add (the default is `msoControlButton` ):
	Type	Control Object
	`msoControlButton`	`CommandBarButton`
	`msoControlPopupCommandBarPopup`
	`msoControlEdit CommandBarComboBox`
	`msoControlDropdown`	`CommandBarComboBox`
	`msoControlComboBox`	`CommandBarComboBox`
`Id`	(optional) An integer that specifies the built-in control you want to add.
`Parameter`	(optional) You use this argument to send a parameter to a built-in control. (The application uses this parameter to modify how it runs the command associated with the control.) For custom controls, you can use this argument to send information to the procedure associated with the control.
`Before`	(optional) The index number of the control before which the new control will be added. If you omit this argument, VBA adds the control to the end of the command bar.
`Temporary`	(optional) A Boolean value that determines when the control is deleted. Use True to have the control deleted when the application is closed; use False to keep the control (this is the default).

For example, the following statement adds a CommandBarButton object to the end of the toolbar named My Toolbar:

 CommandBars("My Toolbar").Controls.Add Type:=msoControlButton

The Command Bar Info Utility

One of the problems you face when working with command bars and controls is that you're often flying blind. For example, you saw earlier that there's no easy way to tell the name of a menu bar or shortcut menu. Similarly, you can't add a built-in control to a command bar unless you know its Id property, but VBA gives you no easy way to determine this property.

To help you out, I put together a small utility that solves this dilemma. I created a form named CommandBarInfo (see Chapter 14.xls on my Web site) that, when run, displays the dialog box shown in Figure 14.5. The idea is that you use the Name list to select the name of a command bar and then use the Caption list to choose a control. The labels beneath this list tell you the control's Id , Type , and Index properties. If the control is a menu bar, the Command Caption list will contain the menu commands. Again, the Id , Type , and Index properties are shown for the selected command.

Figure 14.5. Use the Command Bar Info utility to find out the `Id` property of a built-in control.

graphics/14fig05.jpg

Control Properties

To make your custom controls do something useful, you have to set a few properties. For example, you'll want to specify the procedure to run when the user clicks the control, and you'll probably want to define a ToolTip for your toolbar-based controls. Here's a quick rundown of these and other control properties:

Control .BeginGroup ” Returns True if the specified Control is at the beginning of a group of controls on a command bar. If True, the underlying application displays a separator bar before the Control .

Control .BuiltIn ” Returns True if the specified Control is native to the application; returns False for custom controls.

Control .Caption ” Returns or sets the caption for specified Control . If the control is a menu bar command or a menu command, the Caption property determines the command text, so you should include an ampersand before the letter you want to use as an accelerator key, like so:

 Set newMenu = CommandBars(1).Controls.Add(Type:=msoControlPopup) newMenu.Caption = "&My Menu"

Control .Controls ” If the specified Control is a CommandBarPopup object, this property returns the collection of all the controls on the pop-up. For example, you can use this property to return all the menu items in a pull-down menu.

If the control is a toolbar button, the Caption property sets the default text used as the control's ToolTip. Note, however, that each control also has a ToolTipText property that you can use to manipulate the ToolTip text.

Control .Enabled ” When this property is True, the user can work with the specified Control . The control is disabled when this property is set to False.

Control .FaceId ” If the specified Control is a CommandBarButton object, this property returns or sets the ID number of the icon on the button's face. Note that this number is the same as the control's Id property in most cases.

Control .Id ” As you've seen, this property returns a unique ID for the specified built-in Control . Note that all custom controls return 1 for the Id property.

Control .Index ” Returns the index number in the Controls collection for the specified Control .

Control .List( Index ) ” If the specified Control is a CommandBarComboBox object, this property returns or sets the value of the list item given by Index (where 0 is the first item).

Control .ListCount ” If the specified Control is a CommandBarComboBox object, this property returns the number of items in the list.

Control .ListIndex ” If the specified Control is a CommandBarComboBox object, this property returns or sets the selected item in the list.

Control .OnAction ” Returns or sets the name of the VBA procedure that will execute when the user clicks the specified Control . Listing 14.4 shows a procedure that adds a new command to the Tools menu.

Listing 14.4. A Procedure That Modifies the Tools Menu by Adding a Command to Execute the `RunCommandBarInfo` Procedure

 Sub AddToolsMenuCommand()     Dim cb As CommandBar     Dim menuTools As CommandBarControl     Dim ctrl As CommandBarControl     Dim ctrlExists As Boolean     ctrlExists = False     '     ' Get the Tools menu (ID=30007)     '     Set menuTools = Application.CommandBars.FindControl(Id:=30007)     '     ' Make sure the command doesn't exist     '     For Each ctrl In menuTools.Controls         If ctrl.Caption = "Command &Bar Info" Then             ctrlExists = True             Exit For         End If     Next ctrl     '     ' If the command doesn't exist, add it     '     If Not ctrlExists Then         Set ctrl = menuTools.Controls.Add(Type:=msoControlButton)         With ctrl             .Caption = "Command &Bar Info"             .OnAction = "RunCommandBarInfo"         End With     End If     Set cb = Nothing End Sub ' This procedure runs the CommandBarInfo utility. ' Sub RunCommandBarInfo()     CommandBarInfo.Show End Sub

The procedure first checks to see if the command already exists. It does this by using the FindControl method to find the Tools menu (which the Command Bar Info utility tells us has ID 30007) and then using a For Each...Next loop to check the Caption of each item on the menu. If the command doesn't exist, the Add method tacks it onto the end of the menu, the Caption property is set to Command &Bar Info , and the OnAction property is set to RunCommandBarInfo . The latter procedure appears at the end of the listing, and it just runs the Show method to display the CommandBarInfo form.

Control .ShortcutText ” If the specified Control is a CommandBarButton object that appears on a menu, this property returns or sets the shortcut key text that appears to the right of the control.

Control .Style ” If the specified Control is a CommandBarButton object, this property returns or sets how the application displays the button:

Style	How the Button Is Displayed
`msoButtonAutomatic`	Using the application's default display.
`msoButtonIcon`	With an icon only.
`msoButtonCaption`	With a caption only.
`msoButtonIconAndCaption`	With an icon and a caption to the right of the icon.
`msoButtonIconAndCaptionBelow`	With an icon and a caption below the icon.
`msoButtonIconAndWrapCaption`	With an icon and a caption wrapped onto multiple lines to the right of the icon.
`msoButtonIconAndWrapCaptionBelow`	With an icon and a caption wrapped onto multiple lines below the icon.
`msoButtonWrapCaption`	With a caption only, wrapped onto multiple lines.

Control .Text ” If the specified Control is a CommandBarComboBox object, this property returns or sets the text that appears in the text box part of the control.

Control .ToolTipText ” Returns or sets the ToolTip text for specified Control .

Control .Type ” Returns the object type for the specified Control . To see a complete list of control types, open the code window for the CommandBarInfo form and examine the ControlType function.

Control .Visible ” Returns or sets whether or not the specified Control is visible. Use True to display the control; use False to hide the control.

Control Methods

To complete our examination of controls, this section looks at a few methods associated with controls. With these methods you can copy and move a control, execute the action that underlies a control, set the focus on a control, delete a control, and more. Here's the rundown:

Control .AddItem ” If the specified Control is a CommandBarComboBox object, this method adds an item to the control's list using the following syntax:

 Control.AddItem(  Text, Index  )

`Control`	The control to which you want to add the list item.
`Text`	A string that specifies the item to be added to the list.
`Index`	(optional) The position of the new item in the list. If you omit this argument, VBA adds the item to the end of the list.

Control .Clear ” If the specified Control is a CommandBarComboBox object, this method clears the contents of the list. Note that you can't apply this method to a built-in control.

Control .Copy ” Makes a copy of the specified Control using the following syntax:

 Control.Copy(Bar, Before)

`Control`	The control you want to copy.
`Bar`	(optional) The `CommandBar` object to which you want to copy the control. If you omit this argument, VBA makes a copy of this control on the command bar that contains `Control` .
`Before`	(optional) The index number of the control before which the copied control will be inserted. If you omit this argument, VBA adds the control to the end of the command bar.

Control .Delete ” Deletes the specified Control using the following syntax:

 Control.Delete(Temporary)

`Control`	The control you want to delete.
`Temporary`	(optional) A Boolean value that determines the permanence of the deletion. If you use True, VBA deletes the control, but then restores the control the next time the application is started. If you use False, VBA deletes the control permanently (this is the default).

Control .Execute ” Runs the built-in command or VBA procedure associated with the specified Control .

Control .Move ” Moves the specified Control using the following syntax:

 Control.Move(Bar, Before)

`Control`	The control you want to move.
`Bar`	(optional) The `CommandBar` object to which you want to move the control. If you omit this argument, VBA moves the control to the end of the command bar that contains `Control` .
`Before`	(optional) The index number of the control before which the moved control will be inserted. If you omit this argument, VBA adds the control to the end of the command bar.

Control .RemoveItem ” If the specified Control is a CommandBarComboBox object, this method removes an item from the list using the following syntax:

  Control  .RemoveItem(  Index  )

`Control`	The control from which you want to remove the item.
`Index`	The number of the item that you want to remove.

Control .Reset ” Restores the specified Control to its default state.

Control .SetFocus ” Sets the focus on the specified Control .

The Absolute Minimum

This chapter rounded out your VBA user interface education by showing you how to set up menus and toolbars to run your procedures. In the first part of this chapter, you learned how to use the built-in tools of the Office applications to create new menus and toolbars and to assign procedures to menu commands and toolbar buttons. From there, I showed you how to wield the CommandBars object model to create custom command bars and controls and to modify the application's built-in command bars and controls.

Here's a list of chapters where you'll find related information:

I go through some basic user interface features ”such as the MsgBox and InputBox functions ”in Chapter 12, "Interacting with the User."
The Command Bar Info utility is a custom user form, and the underlying code manipulates various objects in this form. To learn user forms and their objects, see Chapter 13, "Creating Custom VBA Dialog Boxes."