There are two special VBA events associated to macros or even shapes that could be build on automation. These are the Application.OnKey(Procedure), and the expression.OnAction (beeing expression an Excel.Shape, an Excel.FormsControl or even a CommandBar control).
For such events any procedure in the Workbook can be called (has to be declared as Public). And they can even hold parameter(s), none/one/several. It’s very frustrating to deal with such a mess of [‘] and [“] characters, so it’s really come handy to have a call builder procedure. Just paste this code to any module:
Public Function fBuildCaller(ByVal bWorkbookName As Boolean, _ ByVal ProcName As String, _ ParamArray Args() As Variant) As Variant ' Function to build procedure with variable number of arguments ' Take care that if bWorkBookName = True, will be permanent linked to the Workbook Dim oItem As Variant Dim oSubItem As Variant Dim strDebug As String Dim lgRetVal As Long For Each oItem In Args If IsArray(oItem) Then For Each oSubItem In oItem strDebug = strDebug & " """ & oSubItem & """," Next oSubItem Else strDebug = strDebug & " """ & oItem & """," End If Next oItem If bWorkbookName Then lgRetVal = VBA.MsgBox("If bWorkbookName is set to True, will be permanently linked to Workbook, go with it?", _ vbYesNo + vbExclamation, "I N F O") If lgRetVal = vbNo Then bWorkbookName = False End If If strDebug = vbNullString Then strDebug = VBA.IIf(bWorkbookName, "'" & ThisWorkbook.Name & "'!", "") & _ "'" & ProcName & "'" Else strDebug = VBA.IIf(bWorkbookName, "'" & ThisWorkbook.Name & "'!", "") & _ "'" & ProcName & VBA.Mid$(strDebug, 1, Len(strDebug) - 1) & "'" End If fBuildCaller = strDebug End Function Public Function fBuildCaller2(ByVal ProcName As String, _ ParamArray Args() As Variant) As Variant ' Only working for PopUpMenus... better use fBuildCaller ' Has the advantage that "OnAction" is not linked to the WorkBook name Dim oItem As Variant Dim strDebug As String For Each oItem In Args strDebug = strDebug & Chr(34) & oItem + Chr(34) & "," Next If strDebug = vbNullString Then strDebug = ProcName Else strDebug = ProcName & "(" & VBA.Mid$(strDebug, 1, Len(strDebug) - 1) & ")" End If fBuildCaller2 = strDebug End Function
And that’s it.
Note1: as stated, parameter bWorkbookName is noticeable important. If the worksheets changes its name or it’s beeing copy/pasted, the item -with the event declaration-, the link to the original will be carried wherever it the item goes, and so Excel will complain on opening. Better of to set it to “False”.
Note2: In fBuildCaller Arg variable can handle even an array, but it has to be exploded from 2D to 1D, as a linear array. Watch how the oSubItem works to deal with arrays.
Note3: fBuildCaller2 is a similar procedure, but seems it’s only working with CommandBars, it has as advantange the better readibility of the procedure, but has its disadvantages too.