Programming Excel with VBA and .NET

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