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, http://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 (6)

  1. Michael says:

    I would like to know hot you would determine which button was clicked. If you are discovering many buttons and attaching event handlers this becomes an issue to find the sender object.

    Any thoughts?

  2. Michael says:

    Is there any way to get the sender?  If many buttons call CmdBtn_Click (in the case of variable number of buttons)

  3. Mitrix82 says:

    Is there any way to see which button was clicked? As many buttons may report to one function with this method.

  4. Hi Mitrix82,

    I think the easiest approach would be to have separate click handler for each button.

    HTH,

    Praveen.

  5. Hi Michael,

    I am sorry, I don't see a way to determine the button in case same event handler was used.

    -Praveen.

    1. Stefan says:

      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.

Skip to main content