View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
Bill Renaud Bill Renaud is offline
external usenet poster
 
Posts: 417
Default 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