Geek Answers Handbook

Get the value of the drop-down list in VBA and get the name of the drop-down list ... can't be found anywhere?

I created a drop-down list by dragging the combo box onto my sheet from the UserForm toolbar. I assigned him some values ​​from some cells in the book. Now I want some VBA code to access the selected dropdown as a string.

My drop-down list contains only text.

Also, how do I find the name of this newly created drop-down list (it's nowhere in the properties!)?

+6
vba excel-vba excel excel-2003
source share
3 answers
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 CONTROL drop-down list (not a custom form) just look at
    the name field in the upper left corner of the screen is just above column A. It shows the name of the control when you right-click on your control.

  • List2 is a popup list. Therefore, wherever the data on your list is.

    Hope this helps all of you.

+4
source share

Here you can get the string without having to know the name:

 Dim DD As Shape Set DD = ActiveSheet.Shapes(Application.Caller) MsgBox DD.ControlFormat.List(DD.ControlFormat.ListIndex)
+3
source share

This is a clumsy way to do this, 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 element in the DropDowns collection of the Worksheet object that you could iterate over. This will find elements inserted from the Forms toolbar, but not finding elements inserted from the Control Toolbox

0
source share

More articles:


All Articles