How to populate combobox 2 based on combobox 1 in excel vba?

Question

In Excel,

I have two columns one contains city names and another contains employee name. multiple employee can be in each city.

column 1         column 2

Mumbai           Raj Sharma
New delhi        Ankita
Kolkata          Parth
Mumbai           Rahul
Mumbai           Aadesh
Kolkata          Anis
Chennai          M. swami

In user form1 I have two combo boxes.

In combobox 1 I want to populate unique city names (Mumbai, New Delhi, Kolkata, Chennai) from column 1 and in combobox 2 I want all the respective employees.

combobox1        combobox2

Mumbai           Raj Sharma
Mumbai           Rahul
Mumbai           Aadesh

How can I achieve this?

while posting my question I edited both columns and result columns but they are mixed up when posted. please have a look like that. — Kadar Khan

FaneDuru FaneDuru · Accepted Answer · 2020-08-09T12:36:44

Try the next code, please. The Subcan be called during the userForm Initialize event (I will show you how), or from a button Click event. It uses cbCity and cbPeople combo boxes. Please, change their names with the one you use:

the code able to load 'cbCity` combo:

Private Sub testPopulateCombo1()
 Dim sh As Worksheet, LastRow As Long, arr As Variant, El As Variant, j As Long
  Dim arrC As Variant, i As Long, k As Long, boolFound As Boolean
 
  Set sh = ActiveSheet 'use here yor sheet
   LastRow = sh.Range("A" & Rows.count).End(xlUp).Row
   
   arr = sh.Range("A2:A" & LastRow).Value
   ReDim arrC(UBound(arr) - 1)
   'Fill the city names:
   For i = 1 To UBound(arr)
        For Each El In arrC
            If El = "" Then Exit For
            If El = arr(i, 1) Then boolFound = True: Exit For
        Next
        If Not boolFound Then arrC(k) = arr(i, 1): k = k + 1
        boolFound = False
   Next i
   ReDim Preserve arrC(k - 1)
   Me.cbCity.List = WorksheetFunction.Transpose(arrC)
   Me.cbCity.ListIndex = 0 'load the first item in the combo
End Sub

It can be called from the UserForm Initialize event, in this simple way:

Private Sub UserForm_Initialize()
  testPopulateCombo1
End Sub

In order to populate cbPeople combo, when cvCity changes its value, you must use its Change event. Double click on the combo, choose from the top right side Change and paste the next code (over it):

Private Sub cbCity_Change()
  Dim sh As Worksheet, arrP As Variant, i As Long
  
  Set sh = ActiveSheet
   arrP = sh.Range("A2:B" & sh.Range("B" & Rows.count).End(xlUp).Row).Value
   Me.cbPeople.Clear
   For i = 1 To UBound(arrP)
    If arrP(i, 1) = Me.cbCity.Value Then
        Me.cbPeople.AddItem arrP(i, 2)
    End If
   Next
   If Me.cbPeople.ListCount > 0 Then Me.cbPeople.ListIndex = 0
End Sub

If you do not use the combo names I used, please adapt they changing with yours!

During the Initialize event the first combo is loaded and its first element (.ListIndex = 0) will be shown. Then, after each change in cbCity combo, the appropriate people names will be load in cbPeople.

Please, test it and send some feedback. But, if possible, not tomorrow or even later...

How to populate combobox 2 based on combobox 1 in excel vba?

1 Answers