Get dropdown value in VBA and get the name of the

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 the form of a string.

My dropdown contains only text.

Also how do I find the name of this newly created dropdown (it's nowhere in the properties!)?

Answer 1

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 2

Lance Roberts was almost there. If you don't know the name of the drop down that calls the sub, use this:

Dim dd as DropDown
Set dd=ActiveSheet.Shapes(Application.Caller).OLEFOrmat.Object

Dim ddVal as String
ddVal=dd.List(dd.ListIndex)

I used this to create a generic sub for a form with many drop downs.

Answer 3

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 4

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.