Adding Controls to a Worksheet at Runtime

Posted on by David E. Craig

I was working on an issue with one customer and in order to test a hypothesis and see if we could reproduce a problem quickly, I needed to be able to place a lot of VSTO controls on an Excel Spreadsheet in a rapid manner. So I created a Ribbon, put a button on the Ribbon and then added the following code to the button:

// get reference to the Excel application from the ThisAddIn
Excel.Application xlApp = Globals.ThisAddIn.Application;
Tools.Workbook wb = xlApp.ActiveWorkbook.GetVstoObject();
// get the VSTO tools worksheet object
Tools.Worksheet ws = ((Excel.Worksheet)wb.ActiveSheet).GetVstoObject();

// Create a button
System.Windows.Forms.Button btn = new System.Windows.Forms.Button();
btn.Text = "X";
// create an event handler for click
// NOTE: This is an inline delegate
btn.Click += ((pSender, pE) => MessageBox.Show("Hello World"));
// create an event handler for the mouse enter event that
// will pop up a ToolTip.
btn.MouseEnter += ((pSender, pE) =>
{
    // create a tooltip on the fly and attach it to the
    // button so that when the user hovers over it, it shows
    System.Windows.Forms.ToolTip tt = new ToolTip();
    tt.ToolTipIcon = ToolTipIcon.Info;
    tt.SetToolTip(btn, "Hello World!");
});
// Now build a user control, add the button to it and then
// add the control to the form
UserControl uc = new UserControl();
btn.Dock = DockStyle.Fill;
uc.Controls.Add(btn);
// NOTE: When we add the control to the form we need to specify
// the name of the control and to prevent a conflict we name
// the control as a GUID
ws.Controls.AddControl(uc, (Excel.Range)xlApp.Selection,
    Guid.NewGuid().ToString());

 

You may see a new construct for an inline delegate (introduced in C# 3.0). The customer I was working with showed this new method to me and I was amazed at the simplicity: Hot smile

btn.Click += ((pSender, pE) => MessageBox.Show("Hello World"));

 

Well, now with this additional form for a delegate, there are three ways that you could technically write this code:

btn.Click += new EventHandler(btn_Click);
btn.Click += delegate(object pSender, EventArgs pE) { MessageBox.Show("Hellow World!"); };
btn.Click += ((pSender, pE) => MessageBox.Show("Hello World"));

 

The first is the more traditional Event argument using the EventHandler() pre-defined delegate. The second is another form of inline delegate and one I was more familiar with until I was introduced to the new inline delegate form on the 3rd line. This is officially called lambda expressions. You gotta love C#. Open-mouthed smile

2 thoughts on “Adding Controls to a Worksheet at Runtime”

  1. Hello,
    I want to try this, but the last line of code is marked as an error (AddControl). What libraries are you using?
    Thank you

    1. I am using the Tools Libraries. This line exposes that AddControls on the WS object:

      Tools.Worksheet ws = ((Excel.Worksheet)wb.ActiveSheet).GetVstoObject();

      But you need to add this library and then add a using statement: using Tools = Microsoft.Office.Tools.Excel; If that still fails it might be that you need to expose the tools like this, simply: using Microsoft.Office.Tools.Excel; In that case the line above would change to:

      Microsoft.Office.Tools.Excel.Worksheet ws = ((Excel.Worksheet)wb.ActiveSheet).GetVstoObject();

Leave a Reply