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