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.

 

[SamirBhowmik]

Ok, I'll try to write the code tomorrow.

Thank you really for the help!!! Kindly post back on the thread if you have need any clarifications.

Regards

 

[Akuini]

I created an example of 3 interdependent combobox:

How it works:
1. To make a combobox value as the filter criteria for all other combobox (defined in ary array), go to the combobox then DOUBLE-CLICK, choose an item from the list, you'll find the the other combobox will have list that match the filter criteria.
2. You need to adjust sheet name & table name in this part:
Private Const sList As String = "Sheet1"
Private Const sTable As String = "Table1"
3. You need to adjust ary & arT variable in this part:
'ary & arT must be in correct order
'combobox name, the number part, combobox1,combobox2,combobox7
ary = Array(1, 2, 7)
'column number in table, CONTACT-CITY-COMPANY
arT = Array(1, 2, 4)

I intentionally named the third combobox as combobox7 not combobox3, just to show you that the name doesn't have to be sequential. The important thing is the order in both array must be correct. For example in ary the 3rd item is 7 and in arT the 3rd item is 4, it means that combobox7 get the list from column 4 of the the table.

Try applying the code on your workbook, of course you can set more than 3 comboboxes, let me know if you need more help.
Example:

SamirBhowmik - 1.xlsm

Shared with Dropbox

www.dropbox.com

 

[SamirBhowmik]

I created an example of 3 interdependent combobox:

How it works:
1. To make a combobox value as the filter criteria for all other combobox (defined in ary array), go to the combobox then DOUBLE-CLICK, choose an item from the list, you'll find the the other combobox will have list that match the filter criteria.
2. You need to adjust sheet name & table name in this part:
Private Const sList As String = "Sheet1"
Private Const sTable As String = "Table1"
3. You need to adjust ary & arT variable in this part:
'ary & arT must be in correct order
'combobox name, the number part, combobox1,combobox2,combobox7
ary = Array(1, 2, 7)
'column number in table, CONTACT-CITY-COMPANY
arT = Array(1, 2, 4)

I intentionally named the third combobox as combobox7 not combobox3, just to show you that the name doesn't have to be sequential. The important thing is the order in both array must be correct. For example in ary the 3rd item is 7 and in arT the 3rd item is 4, it means that combobox7 get the list from column 4 of the the table.

Try applying the code on your workbook, of course you can set more than 3 comboboxes, let me know if you need more help.
Example:

SamirBhowmik - 1.xlsm

Shared with Dropbox

www.dropbox.com

Hello Akuini,

Thank you a bunch for the wonderful piece of code. I will start adapting to my userform by changing the references. However, do you think we could change the "doubleclick" setting to either a "click" or "dropdown" to load the unique list of elements?

combobox dropdown is preferable but a click would be great too

Regards

 

[Akuini]

However, do you think we could change the "doubleclick" setting to either a "click" or "dropdown" to load the unique list of elements?

No.
Double-click is not to load the unique list of elements, it's to set the combobox as the filter criteria for other combobox. So for example you double-click combobox1 and then click the dropdown arrow then choose an item, the list on other combobox will change accordingly (but you can't see it yet). And after that you can click dropdown on say combobox2 to show its list which is filtered by combobox1 as the criteria. If we change "doubleclick" setting to a "click" then we can't really have a certain combobox as the filter criteria because whenever you click a combobox it becomes the filter criteria.

 

[SamirBhowmik]

No.
Double-click is not to load the unique list of elements, it's to set the combobox as the filter criteria for other combobox. So for example you double-click combobox1 and then click the dropdown arrow then choose an item, the list on other combobox will change accordingly (but you can't see it yet). And after that you can click dropdown on say combobox2 to show its list which is filtered by combobox1 as the criteria. If we change "doubleclick" setting to a "click" then we can't really have a certain combobox as the filter criteria because whenever you click a combobox it becomes the filter criteria.

Hello Akuini,

I have copied your code and tried to imply my requirements by changing the sheets and Table names. However in every attempt, it is crashing the excel file and I get the error message as "Excel has stopped working". Could you kindly have a look at it?

Is it because my Table has more than 20000 lines and its quite big? I have kept the data for 3 months this time for you to try in case

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Thank you in advance

 

[Akuini]

OK, I amended the code to make it faster, but not sure with that many rows of data. Try it:

Comparaison - Copie 2.xlsm

Shared with Dropbox

www.dropbox.com

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Interdependent Comboboxes
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[SamirBhowmik]

OK, I amended the code to make it faster, but not sure with that many rows of data. Try it:

Comparaison - Copie 2.xlsm

Shared with Dropbox

www.dropbox.com

Hello Akuini,

The code is working significantly faster . However, based on the value of the combo boxes the other combox column references are changing. Please look at the screenshot below:

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.

Can you pls modify the code so that the combobox references remain intact and constant ?

Thank you really for your help

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

 

[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