commandbar control and application.caller

[Doug Glancy]

I'm trying to write a CommandbarButton OnAction sub that uses
Application.Caller to toggle the state of the button that called it, even if
it's down a couple levels of submenus.

So if I have a toolbar called "toolbar" and a button called "button" that's
in a submenu I'd like to have the .OnAction of "button" be "toggle_button".
Since, as far as I can tell, an onaction macro doesn't take arguments (can
it?), I'd hard-code "toolbar" in the macro and use (I guess)
application.caller to identify the control.
I've tried a few things: "application.parent" isn't valid, unfortunately, or
I could work my way back up through the menus. I've struggled with a
recursive function and a bunch of nested loops, but it's ugly, and I'm
hoping maybe somebody already has a good solution for this.

Does that make any sense? Of course, I don't really care if the solution
involves application.caller or not.

Thanks in advance,

Doug

 

[Bob Phillips]

Doug,

Here is one way. This checks whether the button is depressed or not and
reverses it. You would add your code as required.

Sub myMacro()
With Application.CommandBars.ActionControl
If .State = msoButtonUp Then
'do one thing
.State = msoButtonDown
Else
'do something else
.State = msoButtonUp
End If
End With

End Sub

You can also pass parameters to OnACtion like so

sAction = "'PERSONAL.XLS'!'PasteComments ""Bob"'"
.OnACtion = sACtion

or you could set the Parameter property and test that in
Application.CommandBars.ActionControl

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Doug Glancy]

Bob,

Wonderful. Now why didn't I just ask these questions at ten o:clock last
night, instead of tying my brain in knots?

Thanks a bunch,

Doug

 

[Bob Phillips]

Doug,

That's why we are here. Glad it helped.

Bob