Create generic macro for a custom toolbar
Try the following routine:
Public Sub Test()
Dim varCaller As Variant
Dim strToolbarName As String
Dim lngControlNumber As Long
varCaller = Application.Caller
If VarType(varCaller) = vbArray + vbVariant _
Then
lngControlNumber = varCaller(1)
strToolbarName = varCaller(2)
End If
End Sub
The best thing I can suggest is to single-step through this code and use
the Locals window to inspect the value of varCaller right after the line
that fetches its value from Application.Caller.
For a more general-purpose routine, you might want to use a Select Case
statement, instead of the If statement that I used for this simple case.
For robust code, you need to use VarType to check to see what type of
Variant was returned from Application.Caller. It will be all different
types, depending on whether your routine was called from a worksheet cell,
a toolbar control, etc. In this case, it is an array of Variants (one
variant holds the Toolbar name and the other variant holds the index number
of the control that was clicked).
--
Regards,
Bill Renaud
|