Command Bar Controls

---

You can add a wide range of command bar controls to a command bar. All these controls have a common set of properties and methods, which are found in the CommandBarControl object. In addition to the CommandBarControl object, there are three other types of control objects: CommandBarButton, CommandBarComboBox, and CommandBarPopup.

CommandBarControls Collection

The CommandBarControls collection contains the set of command bar controls displayed in a command bar. Unlike the CommandBars collection, which had a number of special properties that governed how command bars work in Excel, CommandBarControls is a relatively simple collection object. (See Table 17-3.)

Table 17-3: Key Properties and Methods of the CommandBarControls Collection

Property/Method	Description
Add(Type, Id, Parameter, Before, Temporary)	Method: creates a new command bar object and adds it to the CommandBarControls collection. Type specifies the general type of control that should be added. It can be one of the following: msoControlButton, msoControlEdit, msoControlDropDown, msoControlComboBox, or msoControlPopup; Id is an integer value that specifies a built-in control. A value of 1 or omitting this parameter will add a custom control; Parameter contains programmer-defined information that can be used by the control's OnAction routine to determine how to process this instance of the control; and Before contains the position of the new control in the collection. If omitted, the control will be added at the end of the collection; Temporary, when True, means that the control will be automatically deleted when Excel ends.
Count	Property (read-only): returns the number of CommandBars in the collection.
Item(Index)	Property: returns the command bar object specified by Index.

Although there are many different types of controls (listed in Table 17-4), they can be loosely grouped into four major categories: Normal, Button, Combo Box, and Pop-up. Normal controls have all the properties and methods associated with the CommandBarControl object, whereas Buttons, Combo Boxes, and Pop-ups have additional properties and methods.

Table 17-4: Command Bar Control Types

Constant	Description
msoControlActiveX	Normal
msoControlAutoCompleteCombo	Normal
msoControlButton	Contains a single menu item, an icon button, a menu item with an icon, or an icon with text below the image
msoControlButtonDropdown	Combo Box
msoControlButtonPopup	Pop-up
msoControlComboBox	Combo Box
msoControlCustom	Button
msoControlDropdown	Combo Box
msoControlEdit	Combo Box
msoControlExpandingGrid	Normal
msoControlGauge	Normal
msoControlGenericDropdown	Normal
msoControlGraphicCombo	Combo Box
msoControlGraphicDropdown	Combo Box
msoControlGraphicPopup	Pop-up
msoControlGrid	Normal
msoControlLabel	Normal
msoControlLabelEx	Normal
msoControlOCXDropdown	Combo Box
msoControlPane	Normal
msoControlPopup	Pop-up
msoControlSpinner	Normal
msoControlSplitButtonMRUPopup	Pop-up
msoControlSplitButtonPopup	Pop-up
msoControlSplitDropdown	Combo Box
msoControlSplitExpandingGrid	Normal
msoControlWorkPane	Normal

CommandBarControl Object

CommandBarControl objects represent the individual choices that a user can make. These choices take the form of command buttons, drop-down lists, combo boxes, and many other items. Table 17-5 lists the key properties and methods for the CommandBarControl object.

Table 17-5: Key Properties and Methods of the CommandBarControl Object

Property/Method	Description
BeginGroup	Property: when True, means this control marks the beginning of a group of controls on a control bar.
BuiltIn	Property (read-only): returns True if the command bar is a part of Excel.
Caption	Property: contains the text associated with a command bar control.
Copy (Bar, Before)	Method: creates a copy of the current command bar control to the specified command bar before the specified control. If Bar is not specified, the current bar is assumed. If Before is not specified, the copy will be placed at the end of the command bar.
Delete(temporary)	Method: removes this command bar from the CommandBarControls collection associated with a command bar. Temporary, when True, means that the control will be automatically deleted when Excel ends.
Enabled	Property: when False, means that the control is disabled.
Execute	Method: runs the procedure specified in the OnAction property.
Height	Property: contains the height of the command bar control in pixels.
HelpContextId	Property: contains the help context ID number associated with the command bar control. Remember that the HelpFile property must also be specified for the help subsystem to work properly.
HelpFile	Property: contains the name of the help file associated with the command bar control.
Id	Property: determines the built-in action for a control. Remember that Id is set to 1 for all custom controls.
Index	Property (read-only): returns the relative position of the command bar control in the CommandBarControls collection.
Left	Property (read-only): returns the distance between the left side of the docking area and the command bar control.
Move (Bar, Before)	Method: moves the current command bar control to the specified command bar before the specified control. If Bar is not specified, the current bar is assumed. If Before is not specified, the copy will be placed at the end of the command bar.
OnAction	Property: contains the name of a subroutine that will be run when the user invokes the command bar control. You may also specify a COM add-in by creating a string using the following syntax: '!<add-in-name>', where add-in-name is the name of the COM add in that should be used.
Parameter	Property: contains extra information that can be used by the code associated with the control to modify the default behavior of the control.
Priority	Property: specifies the relative priority of a command bar control so that if there isn't space available in the docking area to fit all the controls, controls with a value of 1 can't be dropped. Valid values range from 0 to 7.
Tag	Property: contains programmer-defined information. This property can be used with the CommandBar.FindControl method to help locate a particular control.
TooltipText	Property: contains text that will be displayed if the user hovers the mouse pointer over the control. By default, the value from the Caption property will be displayed.
Top	Property: contains the distance between the top edge of the command bar control and the top edge of the screen.
Type	Property (read-only): indicates type of the command bar control.
Visible	Property: True when the CommandBarControl is displayed on the screen. Remember that the Enabled property must be True before you can set this property to True.
Width	Property: contains the width of the command bar control in pixels.

When the user clicks or selects a command bar control, the subroutine specified in the OnAction property is fired. You also have the option to execute the routine associated with the command bar control by using the Execute method.

The Parameter and Tag properties are simply areas in which you can store additional information about the specific instance of the control. The Tag property can be useful if you want to locate a specific control based on its Tag value with the FindControl method. You could create a common OnAction routine that performs various actions based on the specific value in the Parameter property for each control.

The Copy and Move methods allow you to relocate controls from one command bar to another, and the Delete method will remove the control from the command bar and the application.

Button Controls

There is only one specific type of button control, which is msoControlButton. This object is useful in situations where you wish to execute a subroutine in response to the user pressing a button or selecting a menu item. Table 17-6 lists the unique properties and methods for the CommandBarButton object when compared with the base CommandBarControl object.

Table 17-6: Unique Properties and Methods of the CommandBarButton Object

Property/Method	Description
BuiltInFace	Property: True if the face of a command bar button is the original face for the button. Setting this value to True will restore the built in face.
CopyFace	Method: copies the current face to the clipboard.
FaceId	Property: contains the Id value for a particular face that will be displayed on the button. A FaceId value of zero means that the command bar button has a custom face.
PasteFace	Method: pastes the face from the clipboard to the command bar button.
ShortcutText	Property: contains text displayed to the caption that represents a shortcut. Remember that this property works only for command bar buttons that have a valid OnAction macro.
State	Property: indicates the visual state of the button. Can be one of the following values: msoButtonDown, msoButtonMixed, or msoButtonUp.
Style	Property: contains the way the button is displayed. msoButtonAutomatic chooses the appropriate style based on the values set in the properties; msoButtonCaption displays the caption as text on the button; msoButtonIcon displays the icon identified by FaceId on the button; msoButtonIconAndCaption displays the caption to the right of the icon; msoButtonIcon- AndCaptionBelow displays the caption below the icon; msoButtonIconAndWrapCaption displays the complete caption to the right of the button even if it has to wrap the text to fit; msoButtonIconAndWrapCaptionBelow displays the text below the icon, wrapping as needed; and msoButtonWrapCaption displays the complete text from the caption, wrapping the text as necessary.

The Style property dictates the way the control will be drawn on the screen. Basically, you can choose to display the text from the Caption property or the icon specified by the FaceId property or both. If you choose both, you can position the caption to the right of the icon or below the icon.

You can choose to display a custom button in any of three different states (up, down, and mixed) using the State property. This is useful if you want to use the button to indicate the status of your code in the same way as the Bold button works. Although the up and down states are self-explanatory, the mixed state is not. This state is useful to reflect a situation where the button should be both up and down, which might occur when you select a range of cells that includes text that is both normal and bold.

Creating Toolbars

A toolbar is a collection of buttons arranged horizontally. You can mix and match various types of buttons together in a single toolbar. (See Figure 17-4.)

Figure 17-4: You can combine multiple types of buttons into a single command bar to create a toolbar.

The following routine was used to create the toolbar shown in Figure 17-4. The code begins by locating the command bar that will hold the controls by using the CommandBars collection and specifying the desired name. Then the routine uses the Add method of the Controls collection to create a new button. The button's Style property is set the proper style for the button, while the Caption property contains text that might or might not be displayed depending on the type of button.

Sub CreateBar()
Dim c As CommandBar
Dim cb As CommandBarButton
 
Set c = Application.CommandBars("Excel2k3 VBA")
 
Set cb = c.Controls.Add(msoControlButton, 2)
cb.Style = msoButtonCaption
cb.Caption = "caption button with a long caption"
 
Set cb = c.Controls.Add(msoControlButton, 3)
cb.Style = msoButtonIcon
cb.Caption = "icon button"
 
Set cb = c.Controls.Add(msoControlButton, 4)
cb.Style = msoButtonIconAndCaption
cb.Caption = "icon and caption button"
End Sub 

Combo Box Controls

There are a number of different variations of the combo box control that you can choose from. (See Table 17-4.) All these variations have one thing in common: the user is allowed to choose from a list of items. All but one of the extra properties and methods are devoted to managing the list of items. (See Table 17-7.)

Table 17-7: Unique Properties and Methods of the CommandBarComboBox Object

Property/Method	Description
AddItem(Text, Index)	Method: adds the value contained in Text to the list at the specified Index position. If Index is omitted, the item is added to the end of the list.
Clear	Method: removes all the list items from the list associated with the combo box.
DropDownLines	Property: contains the number of lines displayed in a drop-down list box or a combo box.
List(Index)	Property: returns a string value representing the item in the list position specified by Index. Remember that the first item in the list has an Index value of 1.
ListCount	Property (read-only): returns the number of items in the list.
ListHeaderCount	Property: contains the number of list items that appear above the separator line.
ListIndex	Property: contains the item number of the currently selected item in the list portion of the combo box control. A value of zero means that nothing is selected.
RemoveItem(Index)	Method: removes the item from the list at the position specified by Index.
Style	Property: specifies the appearance of the control. Can be either msoComboLabel or msoComboNormal.

The list of items that appears in a combo box is essentially a string array. You can retrieve or change an element though the List property by specifying the item's relative location in the list. The ListIndex property points to the currently selected item in the list, whereas the ListCount contains the total number of items in the list.

Items are added to the list using the Add method. You can optionally specify the relative position of the item to be added. All items, starting with the item at the specified location, will be moved one position toward the end of the list. Items are removed from the list by using the RemoveItem method and specifying the relative position of the item to be removed. All subsequent items will be moved one position closer to the start of the list.

Using a Combo Box

Combo boxes and drop-down lists are great ways to allow a user to quickly select from a list of values. The following routine creates a new drop-down list control with three items. The Parameter property is used to identify this particular control, and the OnAction property specifies a macro that will be processed when a value from the drop-down list is selected.

Sub AddCommandCombo()
 
Dim c As CommandBar
Dim cb As CommandBarComboBox
 
Set c = Application.CommandBars("Excel2k3 VBA")
 
Set cb = c.Controls.Add(msoControlDropdown)
cb.Style = msoComboNormal
cb.AddItem "Item 1"
cb.AddItem "Item 2"
cb.AddItem "Item 3"
cb.Parameter = "Dropdown Box #1"
cb.OnAction = "ThisWorkbook.TestControl"
 
End Sub

The OnAction routine shown here is called whenever the user selects an item from the drop- down list. The With statement is used to simplify the reference to the active control that triggered the macro, which is referred to by the CommandBars.ActionControl property. Remember that you have to run both the AddFloatingCommandBar routine and the AddCommandCombo routines in order to test the OnAction event.

Public Sub TestControl()
 
With Application.CommandBars.ActionControl
    If .Parameter = "Dropdown Box #1" Then
        MsgBox .List(.ListIndex)
    
    End If
 
End With
 
End Sub 

Inside the routine, the Parameter property associated with the control is examined to see if it was the drop-down list that was created earlier. If it was, the index of currently selected item is used to extract the appropriate item from the list.

Pop-Up Controls

Pop-up controls are used to display hierarchical information, such as a drop-down menu or list of icons. Unlike the other types of command bar controls in Excel, pop-up controls include two unique properties. (See Table 17-8.)

Table 17-8: Unique Properties of the CommandBarPopup Object

Property/Method	Description
CommandBar	Property (read-only): returns an object reference to a CommandBar object representing the menu for the pop-up.
Controls	Property (read-only): returns an object reference to a CommandBarControls object containing the command bar controls for a pop-up menu.

Displaying a Pop-Up

Displaying pop-up menus is a useful task for many VBA programs. The following routine shows how to create and display a pop-up menu. This routine begins by disabling error trapping by using the On Error Resume Next statement. Then the routine tries to get an object reference to the Excel2k2 VBA Popup command bar. If the command bar object doesn't exist, the variable c will be set to Nothing. Without the On Error statement, any attempt to reference a nonexistent command bar would trigger a run-time error.

Sub ShowCommandPopup()
 
Dim c As CommandBar
Dim cb As CommandBarButton
Dim cp As CommandBarPopup
 
On Error Resume Next
Set c = Application.CommandBars("Excel2k3 VBA Popup")
If c Is Nothing Then
    Set c = Application.CommandBars.Add("Excel2k3 VBA Popup", _
        msoBarPopup, False, True)
    c.Enabled = True
    c.Visible = True
 
    Set cb = c.Controls.Add(msoControlButton)
    cb.Style = msoButtonIconAndCaption
    cb.Caption = "Menu button #1"
    cb.Parameter = "Menu #1"
    cb.OnAction = "ThisWorkbook.TestPopup"
    
    Set cb = c.Controls.Add(msoControlButton)
    cb.Style = msoButtonIconAndCaption
    cb.Caption = "Menu button #2"
    cb.Parameter = "Menu #2"
    cb.OnAction = "ThisWorkbook.TestPopup"
    
    Set cp = c.Controls.Add(msoControlPopup)
    cp.Parameter = "Popup #1"
    cp.Caption = "Submenu choices"
    
    Set cb = cp.Controls.Add(msoControlButton)
    cb.Style = msoButtonIconAndCaption
    cb.Caption = "Submenu button #1"
    cb.Parameter = "Sub menu #1"
    cb.OnAction = "ThisWorkbook.TestPopup"
    
    Set cb = cp.Controls.Add(msoControlButton)
    cb.Style = msoButtonIconAndCaption
    cb.Caption = "Submenu button #2"
    cb.Parameter = "Submenu #2"
    cb.OnAction = "ThisWorkbook.TestPopup"
 
End If
 
c.ShowPopup
 
End Sub 

To create the pop-up menu, a new command bar object of type msoBarPopup must be created. Then the individual controls can be added to the pop-up menu.

Simply adding msoControlButton objects creates the traditional list of menu items. If you wish to display submenu items, you need to add an msoControlPopup item. This item adds an item to the menu with an arrow indicating that there are submenus available. It also acts as a container for the submenu items, which can be added using the same technique as was used for the pop-up bar.

Finally, once the command bar has been initialized, it may be shown using the ShowPopup method. Remember that you need to execute this method each time you want to display the pop-up menu because once the user selects an item from the menu, the menu will disappear. Typically, you display a pop-up menu in response to a user action such as running a particular macro, handling a particular condition with a larger Excel application, or in responding to a key sequence.

Adding Items to an Existing Menu

You can integrate your application into Excel's normal menu structures by adding your own menu items to the standard Excel menu items. For example, the following routine adds an About menu item for a custom VBA application:

Sub AddMenuItem()
 
Dim c As CommandBar
Dim cb As CommandBarButton
Dim cp As CommandBarPopup
 
On Error Resume Next
Set c = Application.CommandBars("Worksheet Menu Bar")
If Not c Is Nothing Then
    Set cp = c.Controls("&Help")
    
    If Not cp Is Nothing Then
        Set cb = cp.Controls.Add(msoControlButton)
        cb.Style = msoButtonCaption
        cb.Caption = "About My VBA App"
        cb.OnAction = "ThisWorkbook.TestMenu"
        
    End If
 
End If
End Sub 

The first step is to locate the command bar where you want to add the new menu item. Because all menu items in Excel are located in the Worksheet menu bar, the easiest way to begin is to locate this command bar. Then within this command bar, an object reference to the &Help pop-up control is located.

Finally, using the &Help pop-up control's Controls collection, a new control button can be added to the end of the list for your application's About message. The OnAction property specifies the routine that will be called to display the about message.

In this chapter, you learned that command bars combine the functions of menu items and toolbars into a single unified system. You learned about the CommandBar object (which can represent a menu or a toolbar) and the CommandBars collection, which contains references to all of the CommandBar objects in your workbook. Then you learned how to add various command bar controls, such as pop-up menus, buttons, and combo boxes to your command bars. Finally, you learned how to define a routine that will respond to an event fired by a command bar control, giving you application an opportunity to perform the task associated with the command bar control. These facilities allow you to create macros and add-ins that appear as if they were part of Excel itself.

---