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

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" wrote in message
...
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,

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" wrote in message
...
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" wrote in message
...
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

Doug,

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

Bob

"Doug Glancy" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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