Excel VBA: Multi-level dependant lists ComboBoxes. some problem in extracting unique lists

talha_ansari

New Member
Joined
Jul 23, 2021
Messages
1
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
i have a file containing the sample userform of 3 sample combo boxes every next combobox is dependant to the previous.

there is a simple vba behind it, which i think should work correctly.

Option Explicit

Private Sub UserForm_Initialize()
' set worksheet
Dim sh As Worksheet
Set sh = Sheets("Clients")
'declare variable
Dim i As Long
For i = 2 To Application.WorksheetFunction.CountA(sh.Cells(1, 1).EntireColumn)
If Application.WorksheetFunction.CountIf(sh.Range("A2", "A" & i), sh.Cells(i, 1)) = 1 Then
Me.ComboBox1.AddItem sh.Cells(i, 1)
End If
Next i
End Sub
Private Sub ComboBox1_Change()
Me.ComboBox2.Clear

' set worksheet
Dim sh As Worksheet
Set sh = Sheets("Clients")
'declare variable
Dim i As Long
For i = 2 To Application.WorksheetFunction.CountA(sh.Cells(1, 1).EntireColumn)
If sh.Cells(i, 1) = Me.ComboBox1.Value And _
Application.WorksheetFunction.CountIf(sh.Range("B2", "B" & i), sh.Cells(i, 2)) = 1 Then

Me.ComboBox2.AddItem sh.Cells(i, 2)

End If
Next i


End Sub

Private Sub ComboBox2_Change()
Me.ComboBox3.Clear

' set worksheet
Dim sh As Worksheet
Set sh = Sheets("Clients")
'declare variable
Dim i As Long
For i = 2 To sh.Range("A10000").End(xlUp).Row
If sh.Cells(i, 1) = Me.ComboBox1.Value And sh.Cells(i, 2) = Me.ComboBox2.Value And _
Application.WorksheetFunction.CountIf(sh.Range("C2", "C" & i), sh.Cells(i, 3)) = 1 Then

Me.ComboBox3.AddItem sh.Cells(i, 3)

End If
Next i

End Sub


it does work for starting items but not for further. the issue is in
Application.WorksheetFunction.CountIf(sh.Range("B2", "B" & i), sh.Cells(i, 2)) = 1

as well as in
Application.WorksheetFunction.CountIf(sh.Range("C2", "C" & i), sh.Cells(i, 3)) = 1

when it try to get unique items for combobox list.

any solution please.


1627085628024.png


1627085685589.png
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,290
Office Version
  1. 365
Platform
  1. Windows
@talha_ansari Welcome to the Forum

Here's an example of 3 dependent combobox in a userform.
Note:
1. Data must be in an actual table (not just in a range)
2. You only need to adjust the code in this part:
'====YOU MAY NEED TO ADJUST THE CODE IN THIS PART:====
'sheet's name where the list is located.
Private Const sList As String = "Sheet1"
'Table name where the list is located
Private Const tbl As String = "Table1"


The sample workbook:

The code
VBA Code:
Dim vList
Dim d As Object

'====YOU MAY NEED TO ADJUST THE CODE IN THIS PART:====
'sheet's name where the list  is located.
Private Const sList As String = "Sheet1"
'Table name where the list  is located
Private Const tbl As String = "Table1"


Private Sub UserForm_Initialize()
vList = Sheets(sList).ListObjects("Table1").DataBodyRange.Columns("A:C")
    Set d = CreateObject("scripting.dictionary")
    d.CompareMode = vbTextCompare
End Sub

Private Sub ComboBox1_Change()
       ComboBox2.Value = ""
       ComboBox3.Value = ""

End Sub

Private Sub ComboBox2_Change()
       ComboBox3.Value = ""

End Sub

Private Sub ComboBox1_DropButtonClick()
Dim i As Long
    d.RemoveAll
    For i = LBound(vList) To UBound(vList)
          d(vList(i, 1)) = Empty
    Next
       ComboBox1.List = d.keys
End Sub


Private Sub ComboBox2_DropButtonClick()
Dim i As Long
    d.RemoveAll
    For i = LBound(vList) To UBound(vList)
    If UCase(vList(i, 1)) = UCase(ComboBox1.Value) Then d(vList(i, 2)) = Empty
    Next
       ComboBox2.List = d.keys
End Sub

Private Sub ComboBox3_DropButtonClick()
Dim i As Long
    d.RemoveAll
    For i = LBound(vList) To UBound(vList)
        If UCase(vList(i, 1)) = UCase(ComboBox1.Value) And UCase(vList(i, 2)) = UCase(ComboBox2.Value) Then
            d(vList(i, 3)) = Empty
        End If
    Next
       ComboBox3.List = d.keys
End Sub
 
Solution

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,434
Cross posted

@ talha_ansari
Please see Forum Rule 13 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.
 
Last edited:

Forum statistics

Threads
1,141,000
Messages
5,703,652
Members
421,308
Latest member
NewBlood

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top