Creating a Custom Command Button Example

The following example creates a custom command button on a specified command bar. You enter the name of the command bar you want to add the command button to and the name for the new button in the SpecifyCommandBarAndButton procedure. This procedure calls the general function CreateCustomButton, which uses the names you entered to add the custom command button to the command bar.

Sub SpecifyCommandBarAndButton()
    CreateCustomButton "Menu Bar", "My custom button"
End Sub

Function CreateCustomButton(strBarName As String, _
        strName As String) As Integer
    Dim cmdBar As CommandBar, cmdBarCustomButton As _
        CommandBarButton

    Set cmdBar = CommandBars(strBarName)
    ' Show command bar if it's not already visible.
    If cmdBar.Visible = False Then cmdBar.Visible = True

    On Error Resume Next

    Set cmdBarCustomButton = cmdBar.Controls(strName)
    If Err <> 0 Then
        ' Custom button doesn't already exist, so create it.
        Set cmdBarCustomButton = cmdBar.Controls _
            .Add(msoControlButton, , , , True)
    End If
    With cmdBarCustomButton
        .Caption = strName
        .BeginGroup = True
        .OnAction = "=msgbox(""Hello World"")"
        ' Run your macro or custom function here.
        .Style = msoButtonCaption
    End With
End Function