Variable Namelist with dependant combobox

[Grimm127]

I have two combo boxes. One is dependant on the other using this code.

If Me.cboRegion.Value = "Owner" Then
Me.cboSubRegion.RowSource = "OwnerDealer"
Me.cboSubRegion.ListIndex = 1
End IF

Me.cboRegion.Value = "Owner 2"
Me.cboBank.RowSource = "OwnerDealer2"
Me.cboBank.ListIndex = 1
End If

My main problem is that I have over 1,000 Owners which means based on my current code I would have to write out over 1,000 rows of data which is not very practical. To make it worse every time I download an update the Owner and Dealer relationship changes as well. Is ther any way to manuver this better?

 

[jbeaucaire]

Create a lookup table somewhere you can reference.

REGION		SUBREGION
Owner		OwnerDealer
Owner2		OwnerDealer2
OwnerBob	BobDealer

Supposing that is stored in columns A:B of a sheet called Regions, lookup the cboRegion.Value in column A, then put the value from column B into the .RowSource

That way it's only a few lines of code, even if you have 100s of regions.

 

[Grimm127]

Thank you for your reply. I am very grateful.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

I have limited expertise with code. I am only just learning it, really learning it and I understand some concepts and how some basic things work. I suppose that I would go into the second combo box and open the code.<o> </o>
<o></o>

REGION: SUB-REGION: NAMED RANGE:
Owner 1 OwnerDealer1 (Name: OwnDeal_1)
Owner 2 OwnerDealer2 (Name:OwnDeal_2)
OwnerBob BobDealer (Name:OwnDeal_3)



OwnDeal_1: OwnDeal_2: OwnDeal_3:
Toyota Honda Mercedes Benz
<o>Honda Jeep Porsche
<o>Ford BMW

So, Combobox1 selects Owner 1<o></o>
<o></o>
Then a Vlookup would search the A:B range and pull out OwnerDealer1, which in turn will show in the combobox 2 – Toyota, Honda, Ford (Own_Deal_1)<o></o>
<o></o>
<o>Then in this format even if I reload, the Named fields won't change just the values which makes this concept work in theory.</o>
<o></o>

<o>I think there needs to be a Hlookup (or should it be a Vlookup) since each dealer has make values. How would you code this Vlookup in VBA code and make this work? </o>
<o></o>
<o>Thank you so much for all your help. I've been really banging my head on this one.</o>
</o></o>

 

[jbeaucaire]

Assuming the first table I described is setup and you named the column A values "REGIONS", then this code would set the listfillrange:

Private Sub ComboBox1_Change()
Dim vFIND As Range

    Set vFIND = Range("REGIONS").Find(Me.ComboBox1.Value, LookIn:=xlValues, LookAt:=xlWhole)
    Me.cboSubRegion.ListFillRange = vFIND.Offset(, 1)

End Sub

 

[Grimm127]

It works. Thank you so much for all your help. I really appricate your time on the matter.