Registering to OnClick event of ActiveX control

问题:

Unfortunately, I have not been able to figure out how to do the following:

I am developing an Excel Add-In in VSTO / .NET / C#.

I have been able to find out how to get references to the ActiveX controls in a worksheet. It is possible via the worksheet.OLEObjects collection.

I can search that collection for a particular button if I know the name of the button. So far, so good.

But now: How can I register an event handler that gets called when the button is clicked?

回答1:

Found a solution now, there: http://blogs.msdn.com/b/vsod/archive/2009/10/02/excel-how-to-run-c-code-behind-with-a-click-of-a-button-on-a-worksheet-without-vba-code.aspx

using Microsoft.VisualBasic.CompilerServices;
using MSForms = Microsoft.Vbe.Interop.Forms;

// ...

// set name of button
var cmdButton = (Excel.Shape) ws.Shapes.Item (1);
cmdButton.Name = "btnClick";

// ...

// get button by name
var cb = (MSForms.CommandButton) NewLateBinding.LateGet (
   ws, // Worksheet object
   null, "btnClick", new object [0], null, null, null
);

// register event handler
cb.Click += click;

// ...

// event handler
void click () {
   // ...
}

回答2:

Not likely this is still relevant for you but might be useful for someone else. I found LateGet to be quite quirky, I use this instead:

foreach (Worksheet sheet in excelApp.Worksheets)
{
    foreach (Shape obj in sheet.Shapes)
    {
        CommandButton xx = sheet.OLEObjects(obj.Name).Object;
        xx.Click += xxClicked;
    }
}

回答3:

I'm looking for the same solution but haven't found yet. What you suggest is to use Form Controls [A] instead of ActiveX Controls [B]:

Otherwise you have to cast them somehow from [A] to [B], when loop through worksheet.OLEObjects. Do you find a way to the same with the ActiveX Controls? I haven't found a way yet to properly reference to their dll as for the forms [A]: ...Microsoft.Vbe.Interop.Forms.dll

As soon as [A] is well references I can access their events. Would be nice to have the same for [B].

By the way, you cannot use (sender as Object, e...) in the event header, makes it difficult what control fired the event in case you using the same handler for multiple buttons.