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

From: http://blogs.msdn.com/ B /vsod/archive/2009/10/02/excel-how-to-run-c-code-behind-with-a-click-of-a-button-on-a-worksheet-without-vba-code.aspx

Excel: How to Run C # code behind with a click of a button on a worksheet, without VBA codemsdnarchive

2 Oct 2009 pm

• 0

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). 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 got strated 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 your y 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 wocould 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