How to handle events from embedded Excel.OleObjects or Excel.Shapes

Question

I'm working on C# and now VB.NET ports of an old VBA program. It has lots of MSForms/OleObjects embedded in it like CommandButton or even images.

My first thought was to declare all the buttons as Microsoft.Vbe.Interop.Forms.CommandButtonbut that leads to a COM exception that the System._COM type can't be cast to ...Forms.CommandButton. If I try a more generic version of this solution, I don't find any items, and if I try to go through all VBComponets I note that they are all the sheets in the workbook, but none of the controls:

foreach (VBComponent xxx in Globals.ThisWorkbook.VBProject.VBComponents) {
    Interaction.MsgBox(xxx.Name);
    Interaction.MsgBox(xxx.ToString);
}

Thus all of these controls are not in .VBComponets, but I can find them as OLEobjects in thisworkbook.worksheets(n).OLEobjects (this is counterintutive to me, but I probably don't understand the system to begin with).

How do I handle the Click action from such an object?

I'm assuming that I need to be using the Excel.OLEObjectEvents_Event interface, but I can't seem to figure out how. If I try to make custom events with delegates, I don't seem to be able to assign them to OleObjects. If I use ActionClickEventHandler.CreateDelegate I can get a huge variety of errors that makes me think that's a dead end.

The official documentation from MS doesn't seem that helpful, though it did introduce me to the idea of Verb, which I'm looking into. So far that has only produced COM errors along the lines of "Application Failed to start."

Even just trying to use one of the two standard events, .GotFocus, I always pull a 0x80040200 error. Example:

Excel.OLEObject ButtonCatcher = Globals.ThisWorkbook.Worksheets(1).OLEObjects("CommandButton1");
ButtonCatcher.GotFocus += CommandButton1_Click;

Throws a COMException Exception from HRESULT: 0x80040200 at the second line. The button is enabled, which is I checked after looking up the code number from the office dev site.

Trying a more generic approach within the code for a sheet containing controls:

object CommandButtonStart = this.GetType().InvokeMember("CommandButton1", System.Reflection.BindingFlags.GetProperty, null, this, null);

Throws a Missing Method error.

Any help is greatly appreciated, this seems like this should be obvious and I'm missing it.

---

**Edit: I have also found that I can cast these controls into Excel.Shape but that doesn't actually get me any closer to running a function or sub from the VSTO. I'm playing with Excel.Shape.OnAction but this requires a VBA sub to be called. Presumably, I could call a VBA sub which calls a sub from the VSTO as long as the VSTO was COM visible. This seems really round-about and I'd only like to do it as a last resort.

Answer 1

Solution Type: VSTO Document-Level

Scenario:
1.) Excel.Worksheet created at run-time. (not a Worksheet Host Item)
2.) Add a button on the Worksheet at run-time that triggers C# code when clicked.

Assembly References:
Microsoft.Vbe.Interop (Microsoft.Vbe.Interop.dll)
Microsoft.Vbe.Interop.Forms (Microsoft.Vbe.Interop.Forms.dll)
Microsoft.VisualBasic (Microsoft.VisualBasic.dll)

Tested / Working Code:

using MSForms = Microsoft.Vbe.Interop.Forms;
using System.Windows.Forms;

...

Microsoft.Vbe.Interop.Forms.CommandButton CmdBtn;

private void CreateOLEButton()
{
   Excel.Worksheet ws = Globals.ThisWorkbook.Application.Sheets["MyWorksheet"];

   // insert button shape
   Excel.Shape cmdButton = ws.Shapes.AddOLEObject("Forms.CommandButton.1", Type.Missing, false, false, Type.Missing, Type.Missing, Type.Missing, 500, 5, 100, 60);
   cmdButton.Name = "btnButton";

   // bind it and wire it up
   CmdBtn = (Microsoft.Vbe.Interop.Forms.CommandButton)Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateGet(ws, null, "btnButton", new object[0], null, null, null);
   CmdBtn.Caption = "Click me!";
   CmdBtn.Click += new MSForms.CommandButtonEvents_ClickEventHandler(ExecuteCmd_Click);
}

private void ExecuteCmd_Click()
{
   MessageBox.Show("Click");
}