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

[talha_ansari]

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.

 

[Akuini]

In this initially when I select the "Source" ComboBox it takes the value of column 1. But when I double-click on the "Client" ComboBox, the "Source" ComboBox reference seems to switch to 13th Column.

Ah, you're right.
Replace Sub toFilter(FN As Long) with this:

Sub toFilter(FN As Long)
Dim c As Range, f As Range, m As Range, x, vb, va
Dim i As Long, j As Long, p As Long, w As Long
Dim a As Long, z As Long, h As Long
Dim tbl As ListObject
Dim CB As String
Set tbl = Sheets(sList).ListObjects(sTable)
a = tbl.Range.Cells(1).Column - 1

i = Application.Match(FN, ary, 0) - 1
z = arT(i)
h = tbl.Range.Cells(1, z).Column
CB = Me.Controls("ComboBox" & FN).Value
    
            With tbl.DataBodyRange.Columns(z)
                .Sort Key1:=.Cells(1), Order1:=xlAscending, Header:=xlYes
            End With
 
        If CB = "" Then
            For Each x In ary
                 Me.Controls("ComboBox" & x).Clear
            Next

            d.RemoveAll
            va = tbl.DataBodyRange.Columns(z)
                For Each x In va
                    d(x) = Empty
                Next

            Me.Controls("ComboBox" & FN).List = d.keys
       Else
            Set m = tbl.DataBodyRange.Columns(z)
        
                Set c = m.Find(What:=CB, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False)
                
                If Not c Is Nothing Then
                        Set f = m.Find(What:=CB, After:=c, SearchDirection:=xlNext)
            
'                        Debug.Print f.Row & " : " & c.Row
                        For w = 0 To UBound(arT)
                            If w <> i Then
                                d.RemoveAll
                                 With Sheets(sList)
                                 vb = .Range(.Cells(f.Row, arT(w) + a), .Cells(c.Row, arT(w) + a))
                                 End With
                                For j = 1 To UBound(vb, 1)
                                        d(vb(j, 1)) = Empty
                                Next
                                Controls("ComboBox" & ary(w)).List = d.keys
                            End If
                        Next

                Else
                
                         For Each x In ary
                             If x <> FN Then Me.Controls("ComboBox" & x).Clear
                        Next
               
                End If
            

        End If

  d.RemoveAll
End Sub

Additionally, is there a posibility to affect a filtering to the table based on the values selected and assign it to the "CommandButton2 - Filter Selection"?

I don't understand what you mean, can you elaborate?

 

[SamirBhowmik]

Ah, you're right.
Replace Sub toFilter(FN As Long) with this:

Sub toFilter(FN As Long)
Dim c As Range, f As Range, m As Range, x, vb, va
Dim i As Long, j As Long, p As Long, w As Long
Dim a As Long, z As Long, h As Long
Dim tbl As ListObject
Dim CB As String
Set tbl = Sheets(sList).ListObjects(sTable)
a = tbl.Range.Cells(1).Column - 1

i = Application.Match(FN, ary, 0) - 1
z = arT(i)
h = tbl.Range.Cells(1, z).Column
CB = Me.Controls("ComboBox" & FN).Value
   
            With tbl.DataBodyRange.Columns(z)
                .Sort Key1:=.Cells(1), Order1:=xlAscending, Header:=xlYes
            End With
 
        If CB = "" Then
            For Each x In ary
                 Me.Controls("ComboBox" & x).Clear
            Next

            d.RemoveAll
            va = tbl.DataBodyRange.Columns(z)
                For Each x In va
                    d(x) = Empty
                Next

            Me.Controls("ComboBox" & FN).List = d.keys
       Else
            Set m = tbl.DataBodyRange.Columns(z)
       
                Set c = m.Find(What:=CB, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False)
               
                If Not c Is Nothing Then
                        Set f = m.Find(What:=CB, After:=c, SearchDirection:=xlNext)
           
'                        Debug.Print f.Row & " : " & c.Row
                        For w = 0 To UBound(arT)
                            If w <> i Then
                                d.RemoveAll
                                 With Sheets(sList)
                                 vb = .Range(.Cells(f.Row, arT(w) + a), .Cells(c.Row, arT(w) + a))
                                 End With
                                For j = 1 To UBound(vb, 1)
                                        d(vb(j, 1)) = Empty
                                Next
                                Controls("ComboBox" & ary(w)).List = d.keys
                            End If
                        Next

                Else
               
                         For Each x In ary
                             If x <> FN Then Me.Controls("ComboBox" & x).Clear
                        Next
              
                End If
           

        End If

  d.RemoveAll
End Sub

I don't understand what you mean, can you elaborate?

Hello Akuini,

Thank you, the code is perfect... regarding the request (Additionally, is there a posibility to affect a filtering to the table based on the values selected and assign it to the "CommandButton2 - Filter Selection"?), I would like to filter the table "DynamicPath_2" based on the selections from any combobox individually or selections from a number of comboboxes using the commandbutton which is named "Filter Selection"
For example:

I want to be able to filter the table based on the selection from a ComboBox individually like "Source" combobox only

or with a combination of multiple selections from the ComboBoxes like "Source", "Name of Project", "Status" and "Client" etc.

I hope it's clear for you. Pls let me know

 

[Akuini]

I tried added it on the end of the new code but get this error:

I want to be able to filter the table based on the selection from a ComboBox individually like "Source" combobox only

or with a combination of multiple selections from the ComboBoxes like "Source", "Name of Project", "Status" and "Client" etc.

That's a different problem, you need to start a new thread.

 

[SamirBhowmik]

That's a different problem, you need to start a new thread.

Ok, I will do that... Thank you so much for the help so far!!!

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.

 

[charly1]

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

3 combobox, dependent, userform, simple, dictionary, table - 1

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.

www.mediafire.com

The 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

hi
I have just used your 3 depended combo, and it has been a major help. Thanks a lot.

My question is that in my case not ALL the possible options in combobox1 one, have a secondary list for combo2. So too, not all the possible selections in Combo 2 have a third list in combo 3. Is there a possiblity to adapt your software that if in the table an item appearing in coloumn 1 has nothing next to it in coloumn 2, then if that selection would be made in combo 1, combo 2 and three would automaticly be disabled. Same with combo 2?

 

[charly1]

Thank You again and again. You are a true excel master.

IF IT DOESN'T ENTAIL TO MUCH EFFORT ON YOUR PART, would it be possible to tweak the code, that whenever the combo boxes are disabled they are greyed out.

 

[charly1]

Hi

Sorry for the delay.
It works beautifully. Thank You so Much

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.