Populating form dropdown at run time
 

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...

Populating form dropdown at run time
 

Sub abc()
With Sheet1.DropDowns("drop down 2")
.ListFillRange = "Sheet1!A1:A10"
End With
End Sub
--
Regards,
Tom Ogilvy


"No_Spam" wrote:

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...

Populating form dropdown at run time
 

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?

Populating form dropdown at run time
 

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

NickHK

"No_Spam"
egroups.com...
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...

Populating form dropdown at run time
 

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.

No_Spam wrote:

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?

--

Dave Peterson

Populating form dropdown at run time
 

Thanks! That saves lots of event writing!!!