One Combobox changing another's rowsource

**johnliquid** · 06-02-2011, 07:52 AM

Hi there, I'm new to VBA and am looking for some assistance!

I have created a userform that has 2 comboboxes, Region, City / County. I would like the comboboxes to pull data from the same sheet.

I have set a static cell range in the rowsource area of the properties box of the Region combobox.

What I would like to happen on the userform is the options available in the drop down menu of the City / County to be determined by the selection made previously in the Region combobox. So I need to use code so that the rowsource of the City / County combobox reacts dynamically to the selection made in the Region combobox.

I have data inputted on columns C - G for the Region combobox to pull from.

Hopefully somebody can help, I've attached a sample workbook, so would appreciate any assistance.

Thanks in advance.

John

**royUK** · 06-02-2011, 08:06 AM

Your lists aren't really ideal for slecting from, you have "sub-headings" in them

Try this,

Private Sub RegionDrpDwn_AfterUpdate()
With Me.CityCountyDrpDwn
    Select Case RegionDrpDwn.ListIndex
        Case 0: .List = Sheets("Info").Range("C4:C40").Value
        Case 1: .List = Sheets("Info").Range("D4:D21").Value
        Case 2: .List = Sheets("Info").Range("E4:E17").Value
        Case 3: .List = Sheets("Info").Range("F4:F12").Value
        Case 4: .List = Sheets("Info").Range("G4:G12").Value
    End Select
    End With
End Sub

**johnliquid** · 06-02-2011, 08:27 AM

Many thanks Roy, that's worked a treat!

Not sure how to mark this as solved though...

Thanks again

John

**royUK** · 06-02-2011, 08:36 AM

For information on how to use the forum please follow one of the links to our how to sections or frequently asked questions!
Navigation bar including the How to...? dropdown
How to videos
Frequently asked questions

I've corrected the code slightly

**johnliquid** · 06-02-2011, 12:22 PM

Hi again,

I will take a look at the forum rules when I get a moment

I've encountered another related issue with code for getting the form to react dynamically to user input in combo boxes.

I now have three combo boxes (Featured Post Area Category, Featured Post Category 1, Featured Post Category 2) whose contents I would like determined by the user selection 'Select Business' combo box.

I've tried to adapt the code supplied earlier, but I don't have the requisite skills yet to make it work!

Thanks in advance for any support, much appreciated!

John