• Ask a Question
150
Insert Image Size must be less than < 5MB.
    Ask a Question
    Cancel
    150
    More answer You can create 5 answer(s).
      Ask a Poll
      Cancel
      Top Contributor

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

      4 Answers
      Keymaster

       

      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/

      Answered by PNRao on July 19, 2016..
      Top Contributor

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

      Answered by Chait on July 19, 2016..
      Keymaster

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

      Thanks-PNRao!

      Answered by PNRao on July 19, 2016..
      Top Contributor

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

      Answered by Chait on July 21, 2016..