CODEWITHSUNDEEP
vbaexcelexcel-2003
EKet
EKet

Reputation: 7314

Get dropdown value in VBA and get the name of the dropdown...nowhere to be found?

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!)?

Upvotes: 8

Views: 80802

Answers (4)

Rhys
Rhys

Reputation: 603

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.

Upvotes: 0

EKet
EKet

Reputation: 7314

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.

Upvotes: 10

Lance Roberts
Lance Roberts

Reputation: 22842

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)

Upvotes: 5

barrowc
barrowc

Reputation: 10679

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

Upvotes: 0

Related Questions