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

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");
}

Can you programmatically add code to a CodeModule in the Workbook, like this?

Private Sub CommonButton_Click(ByVal buttonName As String)
  MsgBox "You clicked button [" & buttonName & "]"
End Sub

Private Sub CreateEventHandler(ByVal buttonName As String)
    Dim VBComp As VBIDE.VBComponent
    Dim CodeMod As VBIDE.CodeModule
    Dim codeText As String
    Dim LineNum As Long

    Set VBComp = ThisWorkbook.VBProject.VBComponents(Me.CodeName)
    Set CodeMod = VBComp.CodeModule
    LineNum = CodeMod.CountOfLines + 1

    codeText = codeText & "Private Sub " & buttonName & "_Click()" & vbCrLf
    codeText = codeText & "  Dim buttonName As String" & vbCrLf
    codeText = codeText & "  buttonName = """ & buttonName & "" & vbCrLf
    codeText = codeText & "  CommonButton_Click buttonName" & vbCrLf
    codeText = codeText & "End Sub"
    CodeMod.InsertLines LineNum, codeText
End Sub

Use the interop forms toolkit. It is free from Microsoft. It provides com wrappers and event messenger class that communicates the event data to and from .NET to VBA. I have used it to handle control events from VBA in .NET, and events from .NET to VBA. You would use the interop

http://www.codeproject.com/Articles/15690/VB-C-Interop-Form-Toolkit.

From the toolkit ducoumentation:

Interop UserControls provide a basic set of intrinsic events (Click, GotFocus, Validate, etc.) in Visual Basic 6.0. You can define your own custom events in Visual Studio .NET and raise them using RaiseEvent. In order to handle the events in Visual Basic 6.0, you need to add a project reference and add a WithEvents variable declaration in your code, and then handle the events using the WithEvents variable rather than the control itself.

How To Handle Interop UserControl Custom Events

1. In Visual Basic 6.0, on the Project menu, click References. Note that there is already a reference to ControlNameCtl, where ControlName is the name of your Interop UserControl.

2. In the Available References list, locate a reference for ControlName and check it, and then click OK.

3. In the Code Editor, add a declaration for a WithEvents variable:

Dim WithEvents ControlNameEvents As ControlLibrary.ControlName

In the Form_Load event handler, add the following code to initialize the WithEvents variable:

Private Sub Form_Load()
    Set ControlNameEvents = Me.ControlNameOnVB6Form
End Sub

Add code to handle your custom event in the WithEvents variable's event handler:

Private Sub ControlNameEvents_MyCustomEvent()
    MsgBox("My custom event fired")
End Sub