Add DropDown at Runtime Visual Studio Shared AddIn

[aferriere]

Hi

I have created a shared add in for Excel using Visual Studio.NET 1.1.
Please note I am NOT using Visual Studio Tools for Office. I am trying
to add a dropdown to the active worksheet at runtime.
I am not able to figure out what code to do so. The following
approaches DONT work

ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False,
_
DisplayAsIcon:=False, Left:=65.25, Top:=40.5, Width:=72,
Height:=18)

ActiveSheet.DropDowns.Add()

From within the code the OLEObjects and DropDowns class dont have the

Add method. I can use the Add method from VBA but not from VS.NET. Is
it even possible to do this in VS.NET ?

Thanks

 

[XL-Dennis]

Hi,

The following snippet code should get You started:

Sub Add_Combobox_Sheet()
Dim xlwbBook As Excel.Workbook
Dim xlwsSheet As Excel.Worksheet
Dim xlObjects As Excel.OLEObjects
Dim xlObject As Excel.OLEObject

xlwbBook = g_xlApp.ActiveWorkbook
xlwsSheet = CType(xlwbBook.ActiveSheet, Excel.Worksheet)
xlObjects = CType(xlwsSheet.OLEObjects, Excel.OLEObjects)

xlObject = CType(xlObjects.Add(ClassType:="Forms.ComboBox.1",
Link:=False, _
DisplayAsIcon:=False, Left:=252.75, Top:=121.5, Width:=105.75,
Height _
:=17.25).Select(), Excel.OLEObject)

xlObjects = Nothing
xlObject = Nothing
xlwsSheet = Nothing
xlwbBook = Nothing
End Sub

---------------
With kind regards,
Dennis
Weekly Blog .NET & Excel: http://xldennis.wordpress.com/
My English site: http://www.excelkb.com/default.aspx
My Swedish site: http://www.xldennis.com/

 

[aferriere]

Thanks Dennis. You pointed me in the right direction.

For any of you guys trying to do something similiar here is the working
code:

string []vaProducts={"Water", "Oil", "Chemicals", "Gas"};
Worksheet xlwsSheet;
xlwsSheet = (Worksheet)_excel.ActiveWorkbook.ActiveSheet;
Range Target;
Target = xlwsSheet.get_Range("A9","A9");
Excel.DropDowns xlDropDowns;
Excel.DropDown xlDropDown;
xlDropDowns = ((Excel.DropDowns)(xlwsSheet.DropDowns(Missing.Value)));
xlDropDown=xlDropDowns.Add((double)Target.Left,(double)Target.Top,(double)Target.Width,(double)Target.Height,true);
for (int i=0;i<vaProducts.Length; i++)
{
xlDropDown.AddItem(vaProducts,i);
}

 

[XL-Dennis]

You're welcome and thanks for coming back and add the solution to the thread.

---------------
With kind regards,
Dennis
Weekly Blog .NET & Excel: http://xldennis.wordpress.com/
My English site: http://www.excelkb.com/default.aspx
My Swedish site: http://www.xldennis.com/