Dependant combo box (country and city case)

[Ronald45]

Hello,

I need help in a very simple schema in a userform. What i need to do is to set up 2 comboboxes, the first one shows the countries, and the second one shows the cities of the countries above. (I want the correct cities to show up when i select the country above)

I have just 2 countries, that i can easily show with "row source" but im struggling making the cities combobox appear. I tried 3 different ways but its still not working.

Dim colonne As Integer
Dim i As Integer, j As Integer

Private Sub Userform_initialize()
colonne = 2
Sheets("L1").Range("B2:C2").Interior.ColorIndex = Clear
Do While Sheets("L1").Cells(2, colonne).Value <> ""
Userform.cboCountry.AddItem Cells(2, colonne).Value
colonne = colonne + 1
Loop
End Sub

Private Sub cboCountry_Change()
i = 2
Userform.cboCity.Clear
Sheets("L1").Range("B2:C2").Interior.ColorIndex = Clear
Do While Sheets("L1").Cells(2, colonne).Value <> ""
If Cells(2, i).Value = cboCountry.Value Then
Cells(2, i).Select
ActiveCell.Interior.ColorIndex = 32
colonne = ActiveCell.Column
End If
i = i + 1
Loop
j = 3
Do While Cells(j, colonne).Value <> ""
Userform.cboCity.AddItem Cells(j, colonne)
j = j + 1
Loop
cboCity.ListIndex = 0
End Sub

Dim colonne As Integer
Dim i As Integer, j As Integer

Private Sub Userform_initialize()
colonne = 2
Sheets("L0").Range("B2:C2").Interior.ColorIndex = Clear
Do While Sheets("L1").Cells(2, colonne).Value <> ""
Userform.cboCountry.AddItem Cells(2, colonne).Value
colonne = colonne + 1
Loop
End Sub

Private Sub cboMarque_Change()
With L0
Col = .Rows(2).Cells.Find(cboCountry.Value, LookAt:=xlWhole).Column
cboCity.Clear
cboCity.List = .Range(.Cells(3, Col), .Cells(.Cells(Rows.Count, Col).End(xlUp).Row, Col)).Value
End With
End Sub

Private Sub UserForm_Initialize()
With cboCountry
.AddItem "Canada"
.AddItem "USA”
End With
End Sub

Private Sub  cboCity_Change()
Dim index As Integer
index = cboCity.ListIndex
 cboCity.Clear
Select Case index
Case Is = Canada
With  cboCity
.AddItem "Toronto"
.AddItem "Ottawa"
.AddItem "Montreal"
.AddItem "Vancouver"
End With

Case Is = USA
With  cboCity
.AddItem "Boston"
.AddItem "Miami"
.AddItem "Akron"
.AddItem "Houston”
End With
End Select
End Sub

Please tell me which one is the easiest (or most reliable) and what should i change to make it work. (L0 is the row sources of the countries table and L1 is just a sheet with each country with its cities below)

If u have easier ideas, feel free to tell. Thanks in advance

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Dependant combo box (country and city case)
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Ronald45]

I found it ! thank you Fluff.

 

[Fluff]

Not sure what you found, but please do not mark a post as the solution when it does not contain one.

 

[Ronald45]

Im sorry, im not used to the forum rules (eventhough i read the rules). I'll make sure it wont happen again.

Here's the solution i found. Thanks.

 

[Ronald45]

Private OL As Worksheet
Private TS As ListObject

Private Sub UserForm_Initialize()
Set OL = Worksheets("Sheet")
Set TS = OL.ListObjects(1)
Me.cboCountry.List = Application.Transpose(TS.HeaderRowRange.Value)
End Sub

Private Sub cboCountry_Change()
Dim COL As Byte

Me.cboCity.Clear
COL = Me.cboCountry.ListIndex + 1
For I = 1 To TS.ListRows.Count
    If TS.DataBodyRange(, COL) <> "" Then Me.cboCity.AddItem TS.DataBodyRange(I, COL)
Next I
End Sub

With "sheet" being the worsheet i worked on and which contained the countries and cities as below.