Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create generic macro for a custom toolbar
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create generic macro for a custom toolbar
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create generic macro for a custom toolbar
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create generic macro for a custom toolbar
Actually, I got it now. Thanks for your help. I used: MsgBox
CommandBars.ActionControl.Tag |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create generic macro for a custom toolbar
Much appreciated thanks.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Any way to change generic icons in quick access toolbar? | Excel Worksheet Functions | |||
Custom Toolbar Macro - Path Name | Excel Discussion (Misc queries) | |||
Custom Macro Toolbar? | Excel Discussion (Misc queries) | |||
How do I create a custom image for a toolbar? | Excel Programming | |||
custom toolbar w/ macro | Excel Discussion (Misc queries) |