Page 397 - Microsoft Office Excel 2003 Programming Inside Out

Part 5: Manipulating Excel Objects
Command Bars

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.

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.
Chapter 17
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 prop­
erties that governed how command bars work in Excel, CommandBarControls is a relatively
simple collection object. (See Table 17-3.)

371