Using CommandBar button caption to pass variable to macro

[Tim Golobic]

Using Office vX ...

I have a CommandBar with 20 buttons. Each button has it's own macro
assigned to it, but the each macro just sends a single variable to
another macro which does the rest of the work.

Instead of having a macro for each button, I would like to have them
all use the same macro and use the Caption of the button as the
variable in the main macro. Although I can retrieve the Caption by
specifying a button in the code (still requires macro for each
button), I can't figure out a way to get the caption of the "clicked
button", regardless of which button is clicked. Thoughts on a
solution?

Hopefully that makes a bit of sense.

Thanks

Tim

 

[Bob Greenblatt]

Using Office vX ...

I have a CommandBar with 20 buttons. Each button has it's own macro
assigned to it, but the each macro just sends a single variable to
another macro which does the rest of the work.

Instead of having a macro for each button, I would like to have them
all use the same macro and use the Caption of the button as the
variable in the main macro. Although I can retrieve the Caption by
specifying a button in the code (still requires macro for each
button), I can't figure out a way to get the caption of the "clicked
button", regardless of which button is clicked. Thoughts on a
solution?

Hopefully that makes a bit of sense.

Thanks

Tim

Have you tried caller?

 

[Tim Golobic]

Have you tried caller?

Not familiar with Caller, not sure of the proper syntax for this case
with the Command Bar. Here's some examples and results:

MsgBox CommandBars("My Toolbar").Controls(1).Caption
works correctly, but explicitly addresses the control, not what I want

MsgBox Caller.Caption
error - Object Required

MsgBox Application.Caller.Caption
error - Object Required

MsgBox CommandBars("My Toolbar").Controls(Caller).Caption
error - subscript out of range

MsgBox CommandBars("My Toolbar").Controls(Application.Caller).Caption
error - type mismatch

Additional insight is greatly appreciated.

Tim

 

[Bob Greenblatt]

Not familiar with Caller, not sure of the proper syntax for this case
with the Command Bar. Here's some examples and results:

MsgBox CommandBars("My Toolbar").Controls(1).Caption
works correctly, but explicitly addresses the control, not what I want

MsgBox Caller.Caption
error - Object Required

MsgBox Application.Caller.Caption
error - Object Required

MsgBox CommandBars("My Toolbar").Controls(Caller).Caption
error - subscript out of range

MsgBox CommandBars("My Toolbar").Controls(Application.Caller).Caption
error - type mismatch

Additional insight is greatly appreciated.

Tim

Application.caller will return the button caption. Test this with msgbox
application.caller

 

[Tim Golobic]

Application.caller will return the button caption. Test this with msgbox
application.caller

Nope, sorry, still get a "type mismatch" using just Application.Caller

 

[Tim Golobic]

Application.caller will return the button caption. Test this with msgbox
application.caller

Nope, sorry, still get a "type mismatch" using just Application.Caller

 

[Tim Golobic]

Application.caller will return the button caption. Test this with msgbox
application.caller

Nope, sorry, still get a "type mismatch" using just Application.Caller

 

[Tim Golobic]

Application.caller will return the button caption. Test this with msgbox
application.caller

Nope, sorry, still get a "type mismatch" using just Application.Caller

 

[Bob Greenblatt]

Nope, sorry, still get a "type mismatch" using just Application.Caller

Well, I don't know why you posted this 5 times, but something is very fishy.
Are you saying that you have several buttons on a sheet that all fire the
same sub, and in that sub application.caller does not provide the button
identification? Is the sub in a module or the sheet's code?
Aplication.caller will not return the button caption, but the button ID,
Button 1, Button 2, etc. Post your code again if it still is not working.

 

[Tim Golobic]

Well, I don't know why you posted this 5 times, but something is very fishy.
Are you saying that you have several buttons on a sheet that all fire the
same sub, and in that sub application.caller does not provide the button
identification? Is the sub in a module or the sheet's code?
Aplication.caller will not return the button caption, but the button ID,
Button 1, Button 2, etc. Post your code again if it still is not working.

Sorry about the multiple posts. When I was refreshing the forum, it
was re-posting the message. I deleted the extras on my end, but that
vast internet ...

Here is the code, short and sweet, in a module:
Sub PastePosition_Audio()
MsgBox Application.Caller
End Sub

This is triggered from a button in a custom CommandBar, not a
UserForm. I haven't bothered to re-map the other 20 buttons until I
can get the first one working. The result is an error 13 - type
mismatch. I would happily take the button ID in lieu of the actual
caption.

Thanks for the ongoing help,

Tim

 

[Bob Greenblatt]

Sorry about the multiple posts. When I was refreshing the forum, it
was re-posting the message. I deleted the extras on my end, but that
vast internet ...

Here is the code, short and sweet, in a module:
Sub PastePosition_Audio()
MsgBox Application.Caller
End Sub

This is triggered from a button in a custom CommandBar, not a
UserForm. I haven't bothered to re-map the other 20 buttons until I
can get the first one working. The result is an error 13 - type
mismatch. I would happily take the button ID in lieu of the actual
caption.

Thanks for the ongoing help,

Tim

OOPS, my mistake. I thought you had a button on a worksheet, not a command
bar. I have played around a little, and searched the internet, and I do not
believe that in Excel for the Mac, you can determine which button activated
the macro. I'm afraid, you'll have to have a simple short macro for each
button that sets a global variable and then call another sub (or passes that
sub an argument).

 

[Tim Golobic]

OOPS, my mistake. I thought you had a button on a worksheet, not a command
bar. I have played around a little, and searched the internet, and I do not
believe that in Excel for the Mac, you can determine which button activated
the macro. I'm afraid, you'll have to have a simple short macro for each
button that sets a global variable and then call another sub (or passes that
sub an argument).

Thank you again.

Tim