Populating form dropdown at run time

[Guest]

I have a dropdown form field in my excel sheet. I want to set the
"Input Range" value at run time based on a selection of another
dropdown. So when a user selects a value in dropdown1, it will populate
the range for dropdown2.


With Sheet1.DropDowns("dropdown2")
.ListFillRange = Sheet1.Range("A1:A10")
End With

This code is not working...

 

[Tom Ogilvy]

Sub abc()
With Sheet1.DropDowns("drop down 2")
.ListFillRange = "Sheet1!A1:A10"
End With
End Sub

 

[Guest]

Excellent. Thank you!

How can I can get the dropdown name that was just changed? If a value
changes in a dropdown, I want to be able to call this generic sub, but
this sub needs to know which drod down called it.. Currently I'm using
the onchange event to pass the drop down name to the sub. Is there a
more generic way of doing it, since I have many drop downs and don't
want to have too many onchange events?

 

[NickHK]

I would guess it is expecting a range as a String, not a Range.
..ListFillRange = "A1:A10"

NickHK

 

[Dave Peterson]

You can use Application.caller to get the name.

Option Explicit
Sub testme()
Dim myDD As DropDown

Set myDD = ActiveSheet.DropDowns(Application.Caller)

With myDD
If .ListIndex > -1 Then
MsgBox .Value & vbLf & .ListIndex & vbLf & .List(.ListIndex) _
& .TopLeftCell.Address(0, 0)
End If
End With

End Sub

You may be able to assign the same sub to all your dropdowns--then use something
else to determine which branch to take.

 

[Guest]

Thanks! That saves lots of event writing!!!