Excel: How to run C# code behind with a click of a button on a Worksheet, without VBA code

OLE object controls such as Command button, Checkbox, etc., allow us to call VBA code behind using OnAction property. However, when a situation arises that we have to call .Net code behind, we cannot use OnAction property, because this property requires a VBA macro to be assigned. In such situations, the option that we can immediately think of is to have VBA macro to call the .Net code, which is possible.

There are scenarios such as one where you want to upgrade your VBA add-in (XLA) to .Net COM/Automation add-in (with no VBA layer in between), which does not allow us to use OnAction property, then this blog post can help you. Because this option does not require us to have VBA layer in between and we can call C#.Net code directly.

I have illustrated this idea using an Excel COM add-in that inserts an OLE command button control on Application start up and I will use it to call its button click event written in C# code behind. To know how to build an Office COM add-in by using Visual C# .NET, please refer to the article, https://support.microsoft.com/kb/302901

Here are the steps:

1. Create an Excel COM Add-in using C#

2. Add the following Using statements in the Connect.cs class,

    using System;
   using Extensibility;
   using System.Runtime.InteropServices;
   using Excel = Microsoft.Office.Interop.Excel;
   using Office = Microsoft.Office.Core;
   using MSForms = Microsoft.Vbe.Interop.Forms;
   using Microsoft.VisualBasic.CompilerServices;
   

3. Add the following code in the OnConnection method of the Connect class. This code will insert a command button onto the Active Worksheet and wire up the Click event for the button, which is written in the C# code behind

    public void OnConnection(object application, Extensibility.ext_ConnectMode connectMode, object addInInst, ref System.Array custom)
         {
               try
               {
   
                   xlApp = (Excel.Application)application;
                   wbs = xlApp.Workbooks; //Get the Workbooks collection
                   wbs.Add(Type.Missing); //Add a new workbook
                   wb = xlApp.ActiveWorkbook; 
                   wsht = (Excel.Worksheet)wb.ActiveSheet;
                   //To insert an OLE Object which of type "CommandButton". We need to use the ProgID for the command button, which is "Forms.CommandButton.1"
                   cmdButton = (Excel.Shape)wsht.Shapes.AddOLEObject("Forms.CommandButton.1", Type.Missing, false, false, Type.Missing, Type.Missing, Type.Missing, 200, 100, 100, 100);
                   //We name the command button, we will use it later
                   cmdButton.Name = "btnClick";
                   //In order to access the Command button object, we are using NewLateBinding class as below
                   CmdBtn = (MSForms.CommandButton)NewLateBinding.LateGet((Excel.Worksheet)xlApp.ActiveSheet, null, "btnClick", new object[0], null, null, null);
   
                   //Set the required properties for the command button
                   CmdBtn.FontSize = 10;
                   CmdBtn.FontBold = true; 
                   CmdBtn.Caption = "Click Me";
                   //Wiring up the Click event
                   CmdBtn.Click += new Microsoft.Vbe.Interop.Forms.CommandButtonEvents_ClickEventHandler(CmdBtn_Click);
   
                   }
               catch (Exception ex)
               {
   
                   System.Windows.Forms.MessageBox.Show(ex.Message);
               }
          }
   

4. Now, we will need to implement the Button Click event handler. Here is the sample code snippet that just displays a message box and writes value to some cells

    void CmdBtn_Click()
   {
               //Adding the event code
               System.Windows.Forms.MessageBox.Show("I am called from C# COM add-in");
               wsht.get_Range("A1", "A10").Value2 = "I am called from C# Add-in";  
   }
   

5. Compile and Build the project, which will add some registry entries to notify Office to load the add-in.

   (Note: on machines with Vista or later, you will need to launch Visual Studio in Administrator mode (To do that, right click Visual Studio Icon-->Run as Administrator), as Vista or later would not allow a program to modify registry entries, when UAC is turned on)

6. Now, the add-in will add a button and upon button click, it will call code from the C# code behind

Comments

• Anonymous
  May 22, 2014
  The comment has been removed

• Anonymous
  May 26, 2014
  Is there any way to get the sender?  If many buttons call CmdBtn_Click (in the case of variable number of buttons)

• Anonymous
  May 26, 2014
  Is there any way to see which button was clicked? As many buttons may report to one function with this method.

• Anonymous
  May 27, 2014
  Hi Mitrix82, I think the easiest approach would be to have separate click handler for each button. HTH, Praveen.

• Anonymous
  May 27, 2014
  Hi Michael, I am sorry, I don't see a way to determine the button in case same event handler was used. -Praveen.

  • Anonymous
    August 26, 2016
    Is there any possibility to do the same for ActiveX Controls ?We have a plenty of Office docs with ActiveX Controls (since there are available in all Office apps) and thinking about moving to VSTO.