In VSTO Addin for Excel how to handle a button click event from active worksheet?

Question

Currently I am developing an Excel 2010 Addin using VB.NET 2010 with VSTO.

The active worksheet has a button, its click event will be handled by its own VBA code.

Now I would like to handle the same button click event in Addin without affecting its original VBA function. Is it possible? And how can I achieve this?

The pre-requisite of this task is that nothing is allowed to be changed on the Excel Template (including its VBA code). That's why I am trying from the event handling by VB.NET.

The trigger point is that the button in the Excel template is clicked, then it will surely raise certain event, and such event will certainly be handled by VBA macro, but I would like to add another listener (event handler) in VB.Net to the same event, so I can do some additional task.

Is there anyone know how to add such event handler?

Thanks.

Not sure I see the point, but you'd make a company visible class in the addin to say application.run(macro name,args) then call that so addin.com class.function to run macro in .net or do you mean migrate the function to .net or handle the click in addin not like _click() in VBA??? — Nathan_Sav
No migration intended, just want to use that particular button click event to trigger the Addin to do some additional task. The existing excel template is an approved form (with Macro), I am not allowed to change anything of it. — Wayne
Just noticed auto correct changed make a com visible to make a company. My solution of Application.Run(x,y,z) from VB.NET should do it. In the past i've created the functions in a non exposed class, then created an exposed wrapper to call these, so the non exposed function would be clsFunctions.fnCLICK_BUTTON and the exposed would be clsVBAFunctions.fnCallVBAButton click, which would be called from VBA — Nathan_Sav
Thanks Nathan, but the pre-requisite of this task is that nothing can be changed on the Excel Template (including its vba code). That's why I am trying from the event handling by VB.NET. — Wayne
I'm lost :) You cant get AddIn to do the call to the existing function if you dont tell it to somehow? — Nathan_Sav

Alex Butenko Alex Butenko · Accepted Answer · 2016-01-20T18:27:56

Here was a similar question.

var cmdButton = (Excel.Shape)xlWorkSheet.Shapes.AddOLEObject("Forms.CommandButton.1", Type.Missing, false, false, Type.Missing, Type.Missing, Type.Missing, 60, 60, 60, 60);

cmdButton.Name = "btnClick";

//var cmdBtn = (Microsoft.Vbe.Interop.Forms.CommandButton)Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateGet((Excel.Worksheet)xlApp.ActiveSheet, null, "btnClick", new object[0], null, null, null);

var cmdBtn = (Microsoft.Vbe.Interop.Forms.CommandButton)Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateGet((Excel.Worksheet)xlApp.ActiveSheet, null, "btnClick", new object[0], null, null, null);
//
//some button formatting codes
//
cmdBtn.Click +=cmdBtn_Click;


void cmdBtn_Click()// Command button click event handler
{
  MessageBox.Show("Test");
}

In your case it is easier, you don't need to create a button, you can just use it's name.

In VSTO Addin for Excel how to handle a button click event from active worksheet?

1 Answers