How to determine the which Command Bar button was clicked

OfficeTips Home || VBA Section || General Section || Download Section || Privacy Policy

How to determine which Command Bar button was clicked?

Make use of the ActionControl property of the command bar object. ActionControl is crucial property to determine which button was pressed. Assume you have three buttons. All invoking the same macro but having unique tags then the code to determine which button was pressed would be something like this

Sub ButtonClick()
Dim cmdBCtl As CommandBarControl
Set cmdBCtl = Application.CommandBars.ActionControl

    ' Compare the tag to determine which button was clicked.
    With cmdBCtl
    Select Case .Tag
    Case "First Button"
            ' Do as required

    Case "Second Button"
            ' Do as required

    Case "Third Button"
            ' Do as required

    End Select
    End With
End Sub

The Parameter property can be similarly used in place of the Tag property.