Adding Controls to a Command Bar

Article
06/14/2014

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.

Adding Controls to a Command Bar

See Also

Additional resources