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.)

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.

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.

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.

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.)

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.)

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.