VBA Cascading Dropdowns in UserForm

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??

Top Contributor Asked on July 18, 2016 in VBA: General.
Add Comment
4 Answer(s)
Best answer

 

Hi, Here is the example file and code:

Create a form and place the three list boxes (Region,Country,Products) and a command button (Exit button) as shown in the screenshot below:

VBA Cascading Dropdowns in UserFormScreenshot of Example Working File: VBA Cascading Dropdowns in UserForm

‘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:

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

Here is the Example file , download and explore:

http://vbaf1.com/q/cascading-dropdowns/

Keymaster Answered on July 19, 2016.
Add Comment

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

Top Contributor Answered on July 19, 2016.
Add Comment

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

Thanks-PNRao!

Keymaster Answered on July 19, 2016.
Add Comment

could you expalin the code. iam not able to figure out exactly

Top Contributor Answered on July 21, 2016.
Add Comment
  • Found this useful?

    Please share using the share button above.

    If you found the answer is best answer for your question, Please mark as 'best answer' by clicking on the right tick mark icon at the left side of the answer.

    Found the answer useful and wants to credit the user, then vote the answer (vote up).

  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.