Excel 2003 Get dropdown value in VBA and get the name of the dropdown...no where to be found?

Question

I created a dropdown by dragging the combo box onto my sheet from the UserForm toolbar. I assigned some values to it from some cells in the book. Now I want some vba code to access the selected dropdown item's value in form of a string.

My dropdown contains only text.

Also how do I find the name of this newly created dropdown...it's no where in the properties!

Thanks!

Answer 1

This is a clunky way of doing it but it should work:

Dim o As Object

For Each o In Worksheets("Sheet1").Shapes
    MsgBox o.Name
Next o

There is also a hidden DropDowns collection member of the Worksheet object that you could iterate over. This will find items inserted from the Forms toolbar but won't find items inserted from the Control Toolbox toolbar

Answer 2

Here's how you get the String without needing to know the name:

Dim DD As Shape

Set DD = ActiveSheet.Shapes(Application.Caller)

MsgBox DD.ControlFormat.List(DD.ControlFormat.ListIndex)

Answer 3

Dim dd As DropDown
Set dd = ActiveSheet.DropDowns("Drop Down 6") 
Set r = Sheet2.Range(dd.ListFillRange)

Set ddValue = r(dd.Value)

NOTES:

• DropDown is not a visible class. You just use it and it works.

• To find the name of the dropdown CONTROL (not userform) just look at
  the name box in the top left corner of your screen just above column A. It says the name of the control when you right click on your control.-

• Sheet2 is where the dropdown list is populated. So wherever your list data is.

  Hope that helps you all.