Adding Controls to a Command Bar
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.
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
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
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. 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. In addition, you can use Microsoft® Visual Basic® for Applications (VBA) code that executes in response to CommandBar and CommandBarControl events.
You can 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
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.
See Also
Working with Command Bars | Working with Command Bar Controls | Adding Controls to a Command Bar | Showing and Enabling Command Bar Controls | Visually Indicating the State of a Command Bar Control | Working with Images on Command Bar Buttons