Chapter 17: Command Bars

---

The ability to manipulate menus in Microsoft Excel is very important when creating custom add-ins. By trapping the appropriate events in an add-in, the add-in can automatically add the appropriate menu items, making the add-in appear to be a standard part of Excel. In this chapter, you'll learn about the various objects associated with menus and toolbars and how to add new menu items, modify existing menu items, and undo the changes your application made, to restore Excel to its unmodified state.

Identifying Parts of the Menu System

Microsoft has developed a very flexible object model that combines menus, command buttons, combo boxes, and pop-up menus into a single extendable system. (See Figure 17-1.) This system separates the visual presentation of the choices to the user from the underlying hierarchical structure menus and buttons, which simplifies the development process.

Figure 17-1: The CommandBar set of objects gives VBA programmers a lot of flexibility to integrate their applications into Excel.

CommandBars Collection

The CommandBars collection contains all the command bars defined in Excel. For the most part, the CommandBars collection is just a normal collection with properties and methods such as Item, Count, and Add. However, it also has some properties that determine how the command bars operate, including enabling or disabling features such as adaptive menus, menu customization, and menu animation.

Table 17-1 contains a list of the key properties and methods of the CommandBars collection.

Table 17-1: Key Properties and Methods of the CommandBars Collection

Property/Method	Description
AdaptiveMenus	Property: when True, means that adaptive menus will be used in Excel.
Add(Name, Position, MenuBar, Temporary)	Method: creates a new command bar object and adds it to CommandBars. Name is the name of the new command bar, Position specifies where the command bar is located, MenuBar, when True, means that the command bar replaces the currently active menu bar. Temporary, when True, means that the command bar will be automatically deleted when Excel ends.
Count	Property (read-only): returns the number of CommandBars in the collection.
DisableCustomize	Property: when True, means that command bars may not be customized by the user.
FindControl(Type, Id, Tag, Visible, Recursive)	Method: returns an object reference to the CommandBarControl object that matches the specified criteria. Type specifies the type of control using the MsoControlType enumeration. Id specifies the name of the control. Tag searches for matches using the control's Tag property. Visible, when True, limits the search to only those controls that are visible; Recursive, when True, searches through the current CommandBar object and all of its pop-up subtoolbars.
Item(Index)	Property: returns the command bar object specified by Index.
LargeButtons	Property: when True, means that toolbar buttons will be displayed larger than normal.
MenuAnimationStyle	Property: specifies how the command bar is animated. Can specify one of the following: msoMenuAnimationNone, msnMenuAnimationRandom, msoMenuAnimationSlide, or msoMenuAnimationUnfold.

The Add method creates an empty command bar object with the specified properties, and the FindControl method searches through all the command bars looking for a control with the specified criteria.

CommandBar Objects

CommandBar objects represent containers in which the individual menu items or icons can be placed. A CommandBar object is referenced through the CommandBars collection. This collection exists for the Application object, as well as other CommandBarControls that can contain other controls, such as a menu control having a submenu. Table 17-2 lists the key properties and methods for the CommandBar object.

Table 17-2: Key Properties and Methods of the CommandBar Object

Property/Method	Description
AdaptiveMenus	Property: when True, means that adaptive menus will be used with this command bar.
BuiltIn	Property (read-only): returns True if the command bar is a part of Excel.
Controls	Property (read-only): returns an object reference to a CommandBarControls collection containing all the controls on the command bar.
Delete	Method: removes this command bar from the CommandBars collection.
Enabled	Property: when True, means that the CommandBar object will be displayed in the list of available command bars.
FindControl(Type, Id, Tag, Visible, Recursive)	Method: returns an object reference to the CommandBarControl object that matches the specified criteria. Type specifies the type of control using the MsoControlType enumeration. (See Table 17-4 for a complete list of the enumeration.) Id specifies the name of the control. Tag searches for matches using the control's Tag property. Visible, when True, limits the search to only those controls that are visible. Recursive, when True, searches through the current CommandBar object and all of its pop-up sub- toolbars.
Height	Property: contains the height of the command bar in pixels.
Index	Property (read-only): returns the relative position of the command bar in the CommandBars collection.
Left	Property: contains the distance between the left edge of the screen and the command bar in pixels.
Name	Property: name of the command bar.
Position	Property: contains the position of the command bar. Can be any of these values: msoBarBottom, msoBarFloating, msoBarLeft, msoBarMenuBar, msoBarPopup, msoBarRight, or msoBarTop.
RowIndex	Property: contains the relative position of a command bar in a docking area.
ShowPopup(x, y)	Method: displays a CommandBar as a shortcut menu at the specified location. If x and y are omitted, the current x and y coordinates from the pointer are used.
Top	Property: contains the distance between the top edge of the CommandBar and the top edge of the screen.
Type	Property (read-only): indicates the command bar's type. msoBarTypeMenuBar means that the command bar contains menu buttons; msoBarTypeNormal means that the command bar displays icons; msoBarTypePopup means that the command bar is a shortcut menu.
Visible	Property: True when the command bar 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 in pixels.

Each CommandBar object represents a collection of menu items and toolbar icons that can be displayed on screen. The exact type is identified by the Type property. The Controls collection contains the set of command bar control objects that are present in the command bar.

The Enabled and Visible properties determine if the command bar is seen by the user and available for use. Right-clicking while the mouse pointer is hovering over any toolbar will list all the command bars that can be displayed. To be included on this list, the command bar must be enabled. If the Visible property is True, the command bar name will be preceded by a check mark on the shortcut menu, indicating that it is visible, too.

You can determine if the command bar was originally included in Excel by checking to see if the BuiltIn property is set to True.

The FindControl method locates a specific control based on the some of the control's property values. This can be extremely useful if the user has moved the control from its original location onto a different CommandBar object.

Listing CommandBar Objects

Excel maintains a large collection of built-in command bars, which you can list using the following routine. (See Figure 17-2.) This routine begins by making the ListCommandBars sheet active and then it uses a For Each loop to scan through the CommandBars collection. (You can change this to 'Sheet3' or any other existing sheet name you specify.) Using the CommandBar object from the For Each statement, the Index, Enabled, Visible, Type, and Name properties are copied to the worksheet beginning in row 4.

Sub ListCommandBars()
Dim c As CommandBar
Dim i As Long
 
Sheets.Item("ListCommandBars").Activate
 
i = 3
For Each c In Application.CommandBars
    i = i + 1
    ActiveSheet.Cells(i, 1) = c.Index
    ActiveSheet.Cells(i, 2) = c.Enabled
    ActiveSheet.Cells(i, 3) = c.Visible
    ActiveSheet.Cells(i, 4) = c.Type
    ActiveSheet.Cells(i, 5) = c.Name
    
Next c
End Sub

Figure 17-2: The Index, Enabled, Visible, Type, and Name properties for each CommandBar object are copied to an Excel worksheet.

Adding a Floating Command Bar

You can add a floating command bar (shown in Figure 17-3) to Excel with the following routine. Notice that the Add method uses the name Excel2k3 VBA, which has embedded spaces. The msoBarFloating value specifies that the bar should not be docked with the other command bars, but instead should be displayed in a separate window that can be moved around by the user.

Sub FloatingCommandBar()
Dim c As CommandBar
 
Set c = Application.CommandBars.Add("Excel2k3 VBA", _
    msoBarFloating, False, True)
c.Enabled = True
c.Visible = True
End Sub

Figure 17-3: A floating command bar is not docked with the other command bars.

Setting the third parameter of the Add method to False means that the command bar will simply be added to the collection. A value of True means that this bar will replace the standard menu bar in the Excel application.

The final parameter in the Add method is set to True, indicating that this command bar is temporary and will automatically be deleted when the user exits Excel.

By default, the command bar is neither enabled nor visible, so to display the command bar it's necessary to Enable it and then make it Visible. It must be done in this order because the bar must be enabled before you can set Visible to True.

Note 

You can also specify that the command bar is a pop-up menu by specifying msoBarPopup as the second parameter to the Add method.

Deleting a Command Bar

Deleting a command bar is merely a matter of locating the command bar by name and calling the Delete method like this:

Sub DeleteBar()
Application.CommandBars("Excel2k3 VBA").Delete
End Sub

Tip 

Clean Up Before You Leave
When building an add-in that includes its own command bars, you should ensure that the command bars are deleted when the add-in is removed. As a general precaution, you might want to specify that the command bar is temporary so that it is removed when Excel ends. However, if you do this, you will need to verify that the command bar doesn't exist when the add-in starts and then explicitly add the command bar if it's missing.

---