Reference Excel Menu Command Name in Macro

[skibum019]

I am creating a custom addin that generates a custom menu in the workbook based upon values returned from a database. I would like to be able to create a single macro that each item references in the onAction method and somehow pass the menu item's id or caption or full object path to the macro, if I can do this then I do not need a separate menu for each of the 100+ menu items that will be in one of the sub menus. Can anyone offer some insight to this?

 

[skibum019]

Found this myself....

*Note, you must add a tag definition to each menu item as you generate it, so you can do a find against it in the following code....

    Dim ctlCurrentControl As CommandBarControl
    Dim strControlName As String
    Set ctlCurrentControl = Application.CommandBars.ActionControl
    strControlName = ctlCurrentControl.Caption
    
    Dim myCTL As CommandBarButton
    Set myCTL = Application.CommandBars.FindControl(msoControlButton, , strControlName)

 

[TonyJollans]

Hi skibum019,

Yes, ActionControl is what you want but I don't quite see why you need to find the control - isn't knowing the caption sufficient for you to do whatever you need - just check [blue]Application.CommandBars.ActionControl.Caption[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[skibum019]

TonyJollans,
Thanks for the input, but I need to reference the entire path of the menu if I don't use the find object, I am dynamically creating submenus, so I use the find to capture the command object, if there is an easier way to do this or reference the entire object rather then just the caption, I would be more then interested in that info.
Thanks again,
Brian

 

[TonyJollans]

Hi Brian,

ActionControl is the Control object - if you want something other than the caption, then use it - no need for extra manipulation.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[