Name

commandbarcontrols.Add([Type], [Id], [Parameter], [Before], [Temporary])

Synopsis

Adds a control to a command bar and returns a reference to the new object.

Argument

Settings

Type

An msoControlType constant for the type of control to create. Can be one of these settings: msoControlButton, msoControlComboBox, msoControlDropdown, msoControlEdit, or msoControlPopup.

Id

The Id property of an existing command to add to the command bar. Use this argument to add built-in commands rather than custom commands.

Parameter

A value to pass to the command via the Parameter property.

Before

The position of the control on the command bar. Default is to insert after the last control on the command bar.

Temporary

True prevents the control from being saved when Excel closes. False saves the control in the user’s .xlb file when Excel closes. Default is False.

The following code adds a custom smiley button to the worksheet menu bar; fortunately it’s only temporary:

Sub AddCommandBarControl( )
    Dim cb As CommandBar, cbc As CommandBarControl
    ' Get a command bar
    Set cb = CommandBars("Worksheet Menu Bar")
    Set cbc = cb.Controls.Add(msoControlButton, , , , True)
    cbc.Caption = "Smiley"
    cbc.FaceId = 1131
    cbc.OnAction = "DontWorry"
End Sub
 
Sub DontWorry( )
    MsgBox "Don't worry, be happy."
End Sub

Get Programming Excel with VBA and .NET now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.