C# excel getting Document level from c# created worksheet

Question

I have been trying to add a combobox or really any form controls to an excel sheet that was created on the fly. I was able to add a combobox to an excel sheet when creating an Excel Workbook project in c# and was able to access Controls.AddComboBox function in the document level (Sheet1) as so: Fig 1

namespace ExcelWorkbook2{
   public partial class Sheet1{
     private void Sheet1_Startup(object sender, System.EventArgs e){
       Microsoft.Office.Tools.Excel.Controls.ComboBox comboBox1  
          =this.Controls.AddComboBox(this.Range["A1", "A1"], "comboBox1");
          comboBox1.Items.Add("1 Item");

this refers to sheet1 This code works fine when creating a workbook project but when I am creating an excel sheet on the fly like so: Fig 2

Application xlApp = new Application();
xlApp.Visible = true;
Workbook wb = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Worksheet ws = (Worksheet)wb.Worksheets[1];

I am not able to go into the WorkSheet (ws) and get "controls.AddCombobox." Putting the worksheet (Fig 2) in the quickwatch says that System.___Comobject the "this" object in the other project (Fig 1) returns the value (ExcelWorkbook2.Sheet1). How can I be able to get to the Controls.AddCombobox which is casted as WorkSheetBase to use in the c# generated worksheet (Fig 2). Thanks

Answer 1

Controls.AddCombobox is only available to the Host Items of the Microsoft.Office.Tools.Excel Namespace. In your case, Sheet1 is a host item that is created at design-time.

Worksheets created at run-time..

...cannot contain any host controls or Windows Forms controls.

To create a ComboBox on a worksheet at run-time that handles events in C# and avoid VBA:

Assembly References:
Microsoft.Vbe.Interop (Microsoft.Vbe.Interop.dll)
Microsoft.Vbe.Interop.Forms (Microsoft.Vbe.Interop.Forms.dll)
Microsoft.VisualBasic (Microsoft.VisualBasic.dll)

Tested / Working Code:

using MSForms = Microsoft.Vbe.Interop.Forms;
using System.Windows.Forms;

...

// insert object shape
Excel.Shape cbo = ws.Shapes.AddOLEObject("Forms.ComboBox.1", Type.Missing, false, false, Type.Missing, Type.Missing, Type.Missing, 20, 1, 100, 20);
cbo.Name = cboName;

// bind it and wire it up
MSForms.ComboBox comboBox = (Microsoft.Vbe.Interop.Forms.ComboBox)Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateGet(ws, null, cboName, new object[0], null, null, null);
comboBox.FontSize = 10;
comboBox.FontBold = true;
comboBox.Change += new MSForms.MdcComboEvents_ChangeEventHandler(comboBox_Changed);

// samle data
comboBox.AddItem("Stackoverflow");
comboBox.AddItem("Cool devs");

...

private void comboBox_Changed()
{
    System.Windows.Forms.MessageBox.Show("Selectiong Changed!");
}

---

To create a Button on a worksheet at run-time that handles events in C# and avoid VBA

Please support with your votes and comments. Thanks, Leo.