Create generic macro for a custom toolbar

[spIky haIred]

Hi all,

How do I pick up the name of a custom toolbar item when it is clicked?
I would like to use it the toolbar for navigation purposes in a
similar way I would if it was called from a button, e.g.

Public Sub GoToSheet()
Worksheets(Application.Caller).Activate
End Sub

If I use this behind a button on a toolbar it will fail.. is there
another way to do it?

Thanks.

 

[Jim Cone]

Application.CommandBars.ActionControl
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"spIky haIred"
wrote in message
Hi all,
How do I pick up the name of a custom toolbar item when it is clicked?
I would like to use it the toolbar for navigation purposes in a
similar way I would if it was called from a button, e.g.

Public Sub GoToSheet()
Worksheets(Application.Caller).Activate
End Sub

If I use this behind a button on a toolbar it will fail.. is there
another way to do it?
Thanks.

 

[spIky haIred]

Hi Jim,

I got a run time error 438 - object does not support property/
method... strange... any idea what i might be doing wrong? i assigned
a macro to a button on the toolbar. The macro was just a simple one
like so:

Public Sub Test
Msgbox Application.CommandBars.ActionControl
End sub

Thanks.

 

[spIky haIred]

Actually, I got it now. Thanks for your help. I used: MsgBox
CommandBars.ActionControl.Tag

 

[Bill Renaud]

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).

 

[spIky haIred]

Much appreciated thanks.