Dependent lists in Userform

**afortes** · 04-20-2010, 08:44 AM

Hi all.

I'm hoping someone can help me with a userform problem. I've taken some code I found on this forum, but I'm having difficulty changing it to do what I need

Is it possible to use a formula like index/match on vba code? I would like to select a town with the first combobox and then to have the second combobox display all the clients that I have on that particular town.

I have attached the file so you can see what I'm trying to do. When I select the first one, It seems to work but not for the other items on combobox1.

Thanks,
Arlete

**romperstomper** · 04-20-2010, 09:27 AM

Try this instead:

Private Sub ComboBox1_Change()
    Dim Col As Long
    Dim blnHeaderRow As Boolean
    Dim cl As Range
    Col = 3
    With Feuil1
        .Range("A1").CurrentRegion.AutoFilter field:=Col, Criteria1:=Me.ComboBox1.Value
        Me.ComboBox2.Clear
        For Each cl In .AutoFilter.Range.Columns(Col).SpecialCells(xlCellTypeVisible)
            If blnHeaderRow = False Then
                blnHeaderRow = True
            Else
                Me.ComboBox2.AddItem .Cells(cl.Row, 1).Value
            End If
        Next cl
        .Range("A1").CurrentRegion.AutoFilter
    End With
End Sub

Private Sub UserForm_Activate()
    Dim cl As Range
    Dim n As Long
    Dim colCites As Collection
    Set colCites = New Collection
    Set rComboSource = Feuil1.Range("C2:C14")
    On Error Resume Next
    
    For Each cl In rComboSource
        colCites.Add cl.Value, cl.Value
    Next cl
    For n = 1 To colCites.Count
        Me.ComboBox1.AddItem colCites(n)
    Next n
End Sub