Working with Command Bar Controls
This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
Each CommandBar object has a CommandBarControls collection, which contains all the controls on the command bar. You use the Controls property of a CommandBar object to refer to a control on a command bar. If the control is of the type msoControlPopup, it also will have a Controls collection representing each control on the pop-up menu. Pop-up menu controls represent menus and submenus and can be nested several layers deep, as shown in the second example below.
In this example, the code returns a reference to the New button on the Standard toolbar:
Dim ctlCBarControl As CommandBarControl
Set ctlCBarControl = Application.CommandBars("Standard").Controls("New")
Here the code returns a reference to the Macros… control on the Macro pop-up menu on the Tools menu on the "Menu Bar" main menu bar:
Dim ctlCBarControl As CommandBarControl
Set ctlCBarControl = Application.CommandBars("Menu Bar").Controls("Tools") _
.Controls("Macro").Controls("Macros...")
Because each pop-up menu control is actually a CommandBar object itself, you can also refer to them directly as members of the CommandBars collection. For example, the following line of code returns a reference to the same control as the previous example:
Set ctlCBarControl = Application.CommandBars("Macro") _
.Controls("Macros...")
Once you have a reference to a control on a command bar, you can access all available properties and methods of that control.
Note When you refer to a command bar control by using the control's Caption property, you must be sure to specify the caption exactly as it appears on the menu. For example, in the previous code sample, the reference to the control caption "Macros..." requires the ellipsis (…) so that it matches how the caption appears on the menu.
Adding Controls
To add a control to a command bar, use the Add method of the Controls collection, specifying which type of control you want to create. You can add controls of the following type: button (msoControlButton), text box (msoControlEdit), drop-down list box (msoControlDropdown), combo box (msoControlComboBox), or pop-up menu (msoControlPopup).
The following example adds a new menu to the "Menu Bar" command bar and then adds three controls to the menu:
Private Sub CBAddMenuDemo()
' Illustrates adding a new menu and filling it with controls. Also
' illustrates deleting a menu control from a menu bar.
'
' In Microsoft Excel, the main menu bar is named "Worksheet Menu Bar"
' rather than "Menu Bar".
Dim strCBarName As String
Dim strMenuName As String
Dim cbrMenu As CommandBarControl
strCBarName = "Menu Bar"
strMenuName = "Custom Menu Demo"
Set cbrMenu = CBAddMenu(strCBarName, strMenuName)
' Note: The following use of the MsgBox function in
' the OnAction property setting will work only with
' command bars in Microsoft Access. In the other Office
' applications, you call built-in VBA functions for the
' OnAction property setting. To call a built-in VBA
' function from a command bar control in the other Office
' applications, you must create a custom procedure that
' uses the VBA function and call that custom procedure in
' the OnAction property setting.
Call CBAddMenuControl(cbrMenu, "Item 1", _
"=MsgBox('You selected Menu1 Control 1.')")
Call CBAddMenuControl(cbrMenu, "Item 2", _
"=MsgBox('You selected Menu1 Control 2.')")
Call CBAddMenuControl(cbrMenu, "Item 3", _
"=MsgBox('You selected Menu1 Control 3.')")
' The menu should now appear to the right of the
' Help menu on the menu bar. To see how to delete
' a menu from a menu bar, press F8 to step through
' the remaining code.
Stop
Call CBDeleteCBControl(strCBarName, strMenuName)
End Sub
The CBAddMenuDemo procedure is available in the modCommandBarCode module in CommandBarSamples.mdb in the ODETools\V9\Samples\OPG\Samples\CH06 subfolder on the Office 2000 Developer CD-ROM.
Note that the CBAddMenuDemo procedure calls three other procedures: CBAddMenu, CBAddMenuControl, and CBDeleteCBControl. CBAddMenu returns the new pop-up menu as a CommandBarControl object. In addition, if the command bar specified by the strCBarName argument does not exist, CBAddMenu creates it. CBAddMenuControl adds a button control to the menu created by CBAddMenu and sets the control's OnAction property to the code to run when the button is clicked. CBDeleteCBControl just removes the menu created in the CBAddMenu procedure. CBAddMenu and CBAddMenuControl are shown below:
Function CBAddMenu(strCBarName As String, _
strMenuName As String) As CommandBarControl
' Add the menu named in strMenuName to the
' command bar named in strCBarName.
Dim cbrBar As CommandBar
Dim ctlCBarControl As CommandBarControl
On Error Resume Next
Set cbrBar = CommandBars(strCBarName)
If Err <> 0 Then
Set cbrBar = CommandBars.Add(strCBarName)
Err = 0
End If
With cbrBar
Set ctlCBarControl = .Controls.Add(msoControlPopup)
ctlCBarControl.Caption = strMenuName
End With
Set CBAddMenu = ctlCBarControl
End Function
Function CBAddMenuControl(cbrMenu As CommandBarControl, _
strCaption As String, _
strOnAction As String) As Boolean
' Add a button control to the menu specified in cbrMenu and set
' its Caption and OnAction properties to the values specified in
' the strCaption and strOnAction arguments.
Dim ctlCBarControl As CommandBarControl
With cbrMenu
Set ctlCBarControl = .Controls.Add(msoControlButton)
With ctlCBarControl
.Caption = strCaption
.OnAction = strOnAction
.Tag = .Caption
End With
End With
End Function
The CBAddMenu and CBAddMenuControl procedures are available in the modCommandBarCode module in CommandBarSamples.mdb in the ODETools\V9\Samples\OPG\Samples\CH06 subfolder on the Office 2000 Developer CD-ROM.
You normally set the OnAction property to the name of a procedure to run when the button is clicked. In the example above, however, the OnAction property is set by using a string that contains the built-in VBA MsgBox function and the text to display in the message box. For an example that illustrates how to have multiple command bar controls of different types call the same procedure, see the CreateCustomCommandbar and MoveRecord procedures in the modEmployeesCmdBar module in the CommandBarSamples.mdb sample file in the ODETools\V9\Samples\OPG\Samples\CH06 subfolder on the Office 2000 Developer CD-ROM. When multiple command bar controls use the same OnAction property setting, you can use the ActionControl property and the Parameter property to determine which command bar button is calling the procedure. You can also use VBA code that executes in response to CommandBar and CommandBarControl events. For more information about these events, see "Working with Command Bar Events" later in this chapter.
To see an example of what you can do with the OnAction, Parameter, and ActionControl properties, open the Employees form in the CommandBarSamples.mdb sample file in the ODETools\V9\Samples\OPG\Samples\CH06 subfolder on the Office 2000 Developer CD-ROM and navigate among the available records by using the two custom toolbar buttons and the combo box control. This example illustrates how to add a CommandBarComboBox object to a command bar, populate it with information stored in a database, and execute code in response to a user's selection from the combo box control.
You can easily add any built-in command bar control to a command bar by using the Id property of the built-in control. The following procedure illustrates a technique to add a built-in control to a command bar.
Function CBAddBuiltInControl(cbrDestBar As CommandBar, _
strCBarSource As String, _
strCtlCaption As String) As Boolean
' This procedure adds the built-in control specified in
' strCtlCaption from the strCBarSource command bar to the
' command bar specified by cbrDestBar.
On Error GoTo CBAddBuiltInControl_Err
If CBDoesCBExist(strCBarSource) <> True Then
CBAddBuiltInControl = False
Exit Function
End If
cbrDestBar.Controls.Add ID:=CBGetControlID(strCBarSource, strCtlCaption)
CBAddBuiltInControl = True
CBAddBuiltInControl_End:
Exit Function
CBAddBuiltInControl_Err:
CBAddBuiltInControl = False
Resume CBAddBuiltInControl_End
End Function
The CBAddBuiltInControl procedure is available in the modCommandBarCode module in CommandBarSamples.mdb in the ODETools\V9\Samples\OPG\Samples\CH06 subfolder on the Office 2000 Developer CD-ROM.
This procedure uses the CBGetControlID procedure to find the Id property of the desired control. The CBGetControlID procedure was also used in the "Working with Images on Command Bar Buttons" section earlier in this chapter. To see this procedure used to build a command bar that uses built-in controls, use the F8 key to step through the code in the CBAddBuiltInCtlDemo procedure, available in the modCommandBarCode module in the CommandBarSamples.mdb file in the ODETools\V9\Samples\OPG\Samples\CH06 subfolder on the Office 2000 Developer CD-ROM.
Note When you specify a control's Id property, you also specify the action the control will take when it is selected and, if applicable, the image that appears on the face of the control. To add a control's image without its built-in action, you specify only the FaceId property. For more information about using the FaceId property, see "Working with Images on Command Bar Buttons" earlier in this chapter.
Showing and Enabling Controls
You specify whether a command bar control appears on a command bar by using its Visible property. You specify whether a command bar control appears enabled or disabled (grayed out) by using its Enabled property. For example, the following two lines of code could be used to toggle the Visible and Enabled properties of the named controls:
Application.CommandBars("Menu Bar").Controls("Edit").Enabled = _
Not Application.CommandBars("Menu Bar").Controls("Edit").Enabled
Application.CommandBars("Formatting").Controls("Font").Visible = _
Not Application.CommandBars("Formatting").Controls("Font").Visible
When a command bar control's Enabled property is False, the control appears on the command bar but is disabled and cannot be manipulated.
To see sample procedures that you can use to toggle the Visible or Enabled properties of any command bar control, see the CBCtlToggleVisible and CBCtlToggleEnabled procedures in the modCommandBarCode module in the CommandBarSamples.mdb file in the ODETools\V9\Samples\OPG\Samples\CH06 subfolder on the Office 2000 Developer CD-ROM.
Indicating the State of a Command Bar Control
Many menu commands or toolbar buttons are used to toggle the state of some part of an application from one condition to another. For example, in Office applications, the Bold button and the Align Left button will appear pressed in or not pressed in, depending on the formatting applied to text at the current selection. You can achieve this same effect with your custom command bar button controls by setting the State property to one of the msoButtonState constants.
Note The State property is read-only for built-in command bar controls.
The following procedure shows how to toggle the State property of a custom command bar button control:
Function CBCtlToggleState(strCBarName As String, _
strCtlCaption As String) As Boolean
' Toggle the State property of the strCtlCaption control
' on the strCBarName command bar. The State property is
' read-only for built-in controls, so if strCtlCaption
' is a built-in control, return False and exit the procedure.
Dim ctlCBarControl As CommandBarControl
On Error Resume Next
Set ctlCBarControl = Application.CommandBars(strCBarName).Controls(strCtlCaption)
If ctlCBarControl.BuiltIn = True Then
CBCtlToggleState = False
Exit Function
End If
If ctlCBarControl.Type <> msoControlButton Then
CBCtlToggleState = False
Exit Function
End If
ctlCBarControl.State = Not ctlCBarControl.State
If Err = 0 Then
CBCtlToggleState = True
Else
CBCtlToggleState = False
End If
End Function
The CBCtlToggleState procedure is available in the modCommandBarCode module in CommandBarSamples.mdb in the ODETools\V9\Samples\OPG\Samples\CH06 subfolder on the Office 2000 Developer CD-ROM.