How to Support for Excel2003 CommandBarsControl Click Event

Mar 22, 2013 at 3:42 PM
Hi,
I need to add Click event to CommandBarsControl (Button) in Excel 2003 to simulate facilities and functions available with the XML CustomUI used for excel >= 2007.

I refer to possibility to call via attribute onAction a function and not a simple macro. In this manner I can make dinamically a swich to call a method based on tag, Id, caption or others attributes present into node xml.

see: <button id="loadFile" label="load values" onAction="RunMacroByID"/>
and

// RunTagMacro helper function
public virtual void RunMacroByID(IRibbonControl control)
{
    if (!string.IsNullOrEmpty(control.Id))
    {
        //object app = ExcelDnaUtil.Application;
        //app.GetType().InvokeMember("Run", BindingFlags.InvokeMethod, null, app, new object[] { "Menu"+control.Id }, new CultureInfo(1033));
        menuItems.GetType().InvokeMember("Menu" + control.Id,
                                         BindingFlags.InvokeMethod | BindingFlags.Instance | BindingFlags.Public,
                                         null, menuItems, null, new CultureInfo(1033));
    }
}

Into current ExceDna.Integration.CustomUI.CommandBarControl implementation the Click event is not supported and the onAction call only a simple macro without parameters. I need to pass a parameter to action called from menu in manner to identify at run time the voice menu caller.

How I can extend Excel-DNA to support also Tag and Click event of CommandBarControl into Xml NameSpace (<customUI xmlns='http://schemas.excel-dna.net/office/2003/01/commandbars'>)??

I see Click Event avability here:
http://msdn.microsoft.com/en-us/library/office/ff862235.aspx

Thanks,
Luca
Coordinator
Mar 22, 2013 at 3:54 PM
Hi Luca,

Extending the xml to do this would be hard.

I think you should use the COM object model to get hold of the right CommandBarButton object, and hook up the event yourself. You should be able to do this from AutoOpen() - I think the CommandBars are already set up by then.
There is a helper method called ExcelDna.Integration.CustomUI.ExcelCommandBarsUtil.GetCommandBars() that will get you started, from there you need to find the "Worksheet Menu Bar", and under that will be your menu. Once you have the right button, adding the event hook should be easy.

You can also investigate the "Distribution\Samples\Ribbon\CommandBarsAllWays.dna" sample, which shows some different approaches to putting the command bars in place.

Regards,
Govert
Mar 22, 2013 at 9:26 PM
ok thanks for your very fast answer.

For the moment, I need a fast solution an so I think that your suggestion is a very good starting point.

In the future I will try to modify and extends the abilities of Excel-dna code starting from method ExcelDna.Integration.CustomUI.ExcelCommandBarsUtil.ApplyControlAttribute adding a case for a "onClick" attribute in manner to add a delegate in conjunction with tag attribute value.

Thanks,
Luca
Mar 22, 2013 at 9:44 PM
ok thanks for your very fast answer.

For the moment, I need a fast solution an so I think that your suggestion is a very good starting point.

In the future I will try to modify and extends the abilities of Excel-dna code starting from method ExcelDna.Integration.CustomUI.ExcelCommandBarsUtil.ApplyControlAttribute adding a case for a "onClick" attribute in manner to add a delegate in conjunction with tag attribute value.

Thanks,
Luca