VBA Cascading Dropdowns in UserForm

VBA Cascading Drop downs in User-form

Question:
I have 3 columns :region country product. I want to  have dependent drop-downs 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 drop-downs and not combo boxes or data validation. Can this be done??
Answer:

 

• 

‘populate the regions on form load and write the code for list-box 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.

Instructions to Run VBA Macro Code or Procedure:

You can refer the following link for the step by step instructions. Instructions to run VBA Macro Code

Other Useful Resources:

Click on the following links of the useful resources. These helps to learn and gain more knowledge. VBA Tutorial VBA Functions List VBA Arrays VBA Text Files VBA Tables VBA Editor Keyboard Shortcut Keys List VBA Interview Questions & Answers Blog