MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Dynamic Combo Box

  Asked By: Don    Date: Dec 13    Category: MS Office    Views: 1998

I'm the slow one in my family... I've found lots of discussion and
proposed solutions for this type of challenge, but I can't get
anything to work.

I have a user form with two combo boxes - Plants and Units.

Combo box #1 has a rowsource of a named range called 'Plants'. Work
so great.

I want combo box #2 to show only the units available at the plant
selected in combo box #1.

Clark (combo box #1)
Unit 1
Unit 2 (selections available in combo box #2)
Unit 3
Lenzie (combo box #1)
Block 1
Block 2 (selections available in combo box #2)

I have named ranges set up for each of the units available in each
plant. What seemed most straightforward was:

if cboplant.value = "Clark" then cbounit.rowsource = "Clark"
(the named range in the spreadsheet)

I have tried everything I could google - it's making me crazy.

Thanks in advance for any assistance.



2 Answers Found

Answer #1    Answered By: Madiha Malik     Answered On: Dec 13

This works for me:

Private Sub cboPlant_Change()
cboUnit.ListIndex = -1
Select Case cboPlant.Value
Case "Clark"
cboUnit.RowSource = "Clark"
Case "Lenzie"
cboUnit.RowSource = "Lenzie"
End Select
End Sub

Answer #2    Answered By: Ned Storm     Answered On: Dec 13

Here comes the slow  part... I use case a lot in SQL, so I understand
that part. The key here looks to be the second line
cboUnit.ListIndex=-1. What am I doing here?

And why does this sub get written as cboPlant_Change instead of

Didn't find what you were looking for? Find more on Dynamic Combo Box Or get search suggestion and latest updates.