Creating a New Command Bar Control

To create and add a command-bar control to a command bar, use the Add method of the CommandBarControls collection. This method returns a CommandBarButton, CommandBarComboBox, or CommandBarPopup object, depending on the value of the Type parameter. The syntax is:

CommandBarControlsObject.Add(Type, Id, Parameter, Before, Temporary)

Type is the type of control to be added to the specified command bar. Table 12-1 shows the possible values for this parameter, along with the corresponding control and the return type of the Add method.

Table 12-1. msoControlType Values for the Type Parameter

Type Parameter (Value)

Control

Returned object

msoControlButton (1)

Button

CommandBarButton

msoControlEdit (2)

Text box

CommandBarComboBox

msoControlDropdown (3)

List box

CommandBarComboBox

soControlComboBox (4)

Combo box

CommandBarComboBox

msoControlPopup (10)

Popup

CommandBarPopup

The optional Before parameter is a number that indicates the position of the new control on the command bar. The new control will be inserted before the control that is at this position. If this argument is omitted, the control is added at the end of the command bar.

To add a so-called custom control of one of the types listed in Table 12-1, set the Id parameter to 1 or leave it out. To add a built-in control, set the Id parameter to the ID number of the control (and leave out the Type argument). We will discuss built-in control IDs, and consider some examples, in the following section.

As with command bars, we can set the optional Temporary parameter to True to make the new command-bar control temporary. It will then be deleted when Excel is closed.

It is very important to note that a CommandBar object does not have a CommandBarControls property, as might be expected. In order to return a CommandBarControls object, we must use the Controls property, as in:

CommandBars("Worksheet Menu bar").Controls

It is equally important to note that, among all of the types of CommandBarControls, one and only one type has a Controls property. In particular, a CommandBarControl of type CommandBarPopup has a Controls property, which provides access to the CommandBarControls collection associated with the corresponding menu for the popup control. As we will see in an upcoming example, the Controls property thus provides the means by which we can add controls to the menu!

0 -1

Responses

  • Amethyst Gamgee
    How to access value of commandbar controls vba?
    7 years ago

Post a comment