Click Event Example

The following example creates a new command bar button on the File menu of the host application that enables the user to save a workbook as a comma-separated value file. (This example works in all applications, but the context of saving as CSV is applicable to Microsoft Excel.)

Private HostApp As Object

Sub createAndSynch()
    Dim iIndex As Integer
    Dim iCount As Integer
    Dim fBtnExists As Boolean
    
    Dim obCmdBtn As Object
    
    On Error GoTo errHandler
       
    Set HostApp = Application
    
    Dim barHelp As Office.CommandBar
    Set barHelp = Application.CommandBars("File")
    fBtnExists = False
    iCount = barHelp.Controls.Count
    For iIndex = 1 To iCount
        If barHelp.Controls(iIndex).Caption = "Save As CSV (Comma Delimited)" Then fBtnExists = True
    
    Next
    Dim btnSaveAsCSV As Office.CommandBarButton
    If fBtnExists Then
        Set btnSaveAsCSV = barHelp.Controls("Save As CSV (Comma Delimited)")
    Else
        Set btnSaveAsCSV = barHelp.Controls.Add(msoControlButton)
        btnSaveAsCSV.Caption = "Save As CSV (Comma Delimited)"
    End If
    
    btnSaveAsCSV.Tag = "btn1"
    btnSaveAsCSVHandler.SyncButton btnSaveAsCSV
    Exit Sub
    
errHandler:
    ' Insert error handling code here
End Sub

The preceding example relies on the following code, which is stored in a class module in the VBA project.

Private WithEvents ButtonClickEvent As Office.CommandBarButton

Private Sub ButtonClickEvent_Click(ByVal Ctrl As Office.CommandBarButton, _
    CancelDefault As Boolean)
    ActiveWorkbook.SaveAs "MyWorkbook.csv", xlCSV
    CancelDefault = True
End Sub

Public Sub SyncButton(btn As Office.CommandBarButton)
    Set ButtonClickEvent = btn
    If Not btn Is Nothing Then
        MsgBox "Synced '" & btn.Caption & "' button events."
    End If
    
End Sub

Private Sub Class_Terminate()
    Set ButtonClickEvent = Nothing
End Sub