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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,567
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Sep 23, 2021
Messages
26
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
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:
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

Well-known Member
Joined
Feb 1, 2016
Messages
3,567
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Sep 23, 2021
Messages
26
Office Version
  1. 365
  2. 2013
Platform
  1. Windows

ADVERTISEMENT

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


Thank you in advance
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,567
Office Version
  1. 365
Platform
  1. Windows
OK, I amended the code to make it faster, but not sure with that many rows of data. Try it:
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
66,064
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Sep 23, 2021
Messages
26
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
OK, I amended the code to make it faster, but not sure with that many rows of data. Try it:
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"?
 

Attachments

  • VBA Screenshot.JPG
    VBA Screenshot.JPG
    49.7 KB · Views: 1

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,567
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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

New Member
Joined
Sep 23, 2021
Messages
26
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
Ah, you're right.
Replace Sub toFilter(FN As Long) with this:
VBA Code:
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
 

Forum statistics

Threads
1,148,368
Messages
5,746,287
Members
424,006
Latest member
Metal_warrior

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