Creating a dropdown in Excel using VB.NET

[jcallinan]

Hello, Im looking for a way to create a dropdown in an excel spreadsheet using vb.net - Ive created spreadsheets and assigned values to cells, but never made a dropdown, and Im not having much luck finding any info online. Thanks!

 

[donnacha]

Why dont you use an excel template file and put the drop down in that where you need it. I dont know if you can do it the way you are trying to do it.

 

[Mike_R]

I like Donnachas idea to pre-make the drop-downs. If you really need to make them on the Worksheet dynamically, then the best way to learn this kind of command is to use the Macro Recorder in VBA. You access it from within Excel using Alt|Tools|Macros|RecordMacros... Then do whatever you need to, stop the recorder, and then hit Alt+F11 to see your code within the VBA-IDE.

There are also two types of DropDowns: (1) from the Forms toolbar and from (2) the Controls Toolbox toolbar. Using the Macro Recorder, I got the following for the Forms toolbar version:

ActiveSheet.DropDowns.Add(149.25, 132, 75, 51.75)

For the Controls Toolbox version, I got:

    ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
        DisplayAsIcon:=False, Left:=303.125, Top:=144.375, Width:=84.375, _
        Height:=58.75)

Hopefully this should translate through the COM Interop, but I think it should. Although, if you are using Option Strict On (which is recommended) you will need to add some DirectCast() calls...

-- Mike