VBA Cascading Dropdowns in UserForm

VBA Cascading Dropdowns in UserForm

Question:
I have 3 columns :region country product. I want to  have dependent dropdowns like countries population based on region selection and products population based on countries drop-down. Now here’s the most important part- This should be done using FORM CONTROL dropdowns and not combo boxes or data validation. Can this be done??
Answer:

 

‘populate the regions on form load and write the code for listbox change event

'1. Populate the Regions when you show the Form
Private Sub UserForm_Initialize()
lRow = 455 ' This is last row of your data sheet
For iCntr = 2 To lRow
If Range("A" & iCntr) <> Range("A" & iCntr - 1) Then 'To avoid duplication
lstRegion.AddItem Range("A" & iCntr)
End If
Next
End Sub
'2. Populate the Countries when you change a Regions
Private Sub lstRegion_Change()
lRow = 455 ' This is last row of your data sheet
lstCountry.Clear
lstProducts.Clear
For iCntr = 2 To lRow
If Range("A" & iCntr) = lstRegion.Value And Range("B" & iCntr) <> Range("B" & iCntr - 1) Then
lstCountry.AddItem Range("B" & iCntr)
End If
Next
End Sub
'3. Populate the Products when you change the country
Private Sub lstCountry_Change()
lRow = 455 ' This is last row of your data sheet
lstProducts.Clear
For iCntr = 2 To lRow
If Range("A" & iCntr) = lstRegion.Value And Range("B" & iCntr) = lstCountry.Value Then
lstProducts.AddItem Range("C" & iCntr)
End If
Next
End Sub
'4. Exit Button
Private Sub CommandButton1_Click()
Unload Me
End Sub

Finally Show your user form using the below code in a module. And assign to a button:

'Call the Userform using a button Sub sbShowForm() UserForm1.Show End Sub 

Here is the Example file , download and explore:


Question: thank you…but can this done not using userform and be done using proper dropdowns in EXCEL using vba?


Answer: Yes, it’s possible. Populate the regions on workbook open event.  Use the same controls and code in the worksheet.

1 thought on “VBA Cascading Dropdowns in UserForm”

Leave a Comment

Your email address will not be published. Required fields are marked *