Get dropdown value in VBA and get the name of the dropdown...nowhere 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 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

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.

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)