Determining what menu item called a macro

[Stephen Lloyd]

I'm creating an xla that will add a custom menu that will be added to the
main menu bar. My hope is to have several of the menu items actually tied to
the same macro which will run (portions of the) code conditionally based on
the calling menu item.

Is there a property to determine what menu item called a routine? If so,
what is it and are there any limitations I should be aware of? Any
additional information would be awesome...i.e., obect/collection information,
etc.

I didn't find much in Excel's help files.

 

[Wigi]

Hi

You can use the OnAction methode of a menu item, AND pass some parameters
along to a general procedure. I know it can be done since in the past I
already coded something in that spirit.

I just googled:

excel vba onaction parameter

and have a look at these links, and start with the article from MVP Tushar
Mehta.

 

[Jim Thomlinson]

Take a look at application.caller

 

[Stephen Lloyd]

Thank you both for your responses.

Jim, I had looked at application.caller and thought that it would not return
useful information if vba was called from a menu. I will look at it again
though.

Wigi, thank you for your help as well. I've looked at Mehta's stuff before
and found him helpful.

 

[Dave Peterson]

I think you want actioncontrol, as in:

msgbox Application.CommandBars.ActionControl.Caption

 

[Stephen Lloyd]

and Dave Peterson comes through on the river. Thank you sir. One follow up
question. Is there any reason for which one should prefer to use any one of
the FaceId, Tag, or Caption properties in lieu of the other two?

 

[Dave Peterson]

The .tag property is something you can use that is invisible to the user.

I'd use the .tag property.

And if, er, when, you get complaints from the user to change the caption, you
can fix one thing without having to worry about what else you broke.

And if you have lots of controls that do the same kind of thing, you could add
the same tag to each.

(I can't imagine having to remember the cryptic FaceID's <vbg>.)