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

T

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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
charly1 said:
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?
Click to expand...

Ok, try this:

www.mediafire.com

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

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:
VBA Code: 
Option Explicit
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
      If d.Exists("") Then d.Remove ""
       ComboBox1.List = d.keys
       ComboBox2.Enabled = True
       ComboBox3.Enabled = True
End Sub
Private Sub ComboBox2_Enter()
to_list_Cbo2
End Sub

Private Sub ComboBox2_DropButtonClick()
to_list_Cbo2
End Sub


Sub to_list_Cbo2()
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
       If d.Exists("") Then d.Remove ""
       With ComboBox2
        If d.Count = 0 Then
             .Clear
             .Enabled = False
        Else
             .List = d.keys
             ComboBox3.Enabled = True
        End If
       End With
End Sub

Private Sub ComboBox3_Enter()
to_list_Cbo3
End Sub

Private Sub ComboBox3_DropButtonClick()
to_list_Cbo3
End Sub


Sub to_list_Cbo3()
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
       If d.Exists("") Then d.Remove ""
       With ComboBox3
            If d.Count = 0 Then
                 .Clear
                 .Enabled = False
            Else
                   .List = d.keys
            End If
       End With
End Sub
 
Upvote 1
charly1 said:
would it be possible to tweak the code, that whenever the combo boxes are disabled they are greyed out.
Click to expand...
Try this one:
VBA Code: 
Option Explicit
Dim vList
Dim d As Object

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

Private Sub ComboBox1_Change()
       ComboBox2.Value = ""
       ComboBox3.Value = ""
       
       to_disable ("ComboBox2")
       to_disable ("ComboBox3")
       
       If ComboBox1.ListIndex > -1 Then to_list_Cbo2
 
End Sub

Private Sub ComboBox2_Change()
       ComboBox3.Value = ""
       to_disable ("ComboBox3")
       
       If ComboBox2.ListIndex > -1 Then to_list_Cbo3
End Sub

Private Sub ComboBox1_Enter()

Dim i As Long
    d.RemoveAll
    For i = LBound(vList) To UBound(vList)
          d(vList(i, 1)) = Empty
    Next
      If d.Exists("") Then d.Remove ""
       ComboBox1.List = d.keys
End Sub

Sub to_enable(cbo As String)
With Me.Controls(cbo)
    .Enabled = True
    .BackColor = vbWhite
End With
End Sub

Sub to_disable(cbo As String)
With Me.Controls(cbo)
    .Enabled = False
    .BackColor = 14277081
End With
End Sub

Sub to_list_Cbo2()
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
       If d.Exists("") Then d.Remove ""
       With ComboBox2
        If d.Count = 0 Then
            to_disable ("ComboBox2")
        Else
            to_enable ("ComboBox2")
            .List = d.keys
        End If
       End With
End Sub


Sub to_list_Cbo3()
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
       If d.Exists("") Then d.Remove ""
       
        If d.Count = 0 Then
            to_disable ("ComboBox3")
        Else
             to_enable ("ComboBox3")
             ComboBox3.List = d.keys
        End If
       
End Sub
 
Upvote 1
@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:
www.mediafire.com

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
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
 
Upvote 0
Solution
Cross posted

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

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 =...
www.excelforum.com

@ 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:
Upvote 0
Akuini said:
@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:
www.mediafire.com

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
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
Click to expand...
Hello,

Your code helped me to start structuring my comboboxes and their dependencies. However, could we have this as a dynamic dependency as in all the comboboxes would contain the unique values from the columns and their contents are effected as per the selection in any of the comboboxes?

Thank you in advance for your help!!!
 
Upvote 0
SamirBhowmik said:
However, could we have this as a dynamic dependency as in all the comboboxes would contain the unique values from the columns and their contents are effected as per the selection in any of the comboboxes?
Click to expand...
By "dynamic dependency " do you mean interdependency? for example if combobox1 changes then combobox2 changes and vice versa?
Could you upload a sample workbook (without sensitive data) to a sharing site like dropbox.com or google drive?
And then share the link here.
 
Upvote 0
Akuini said:
By "dynamic dependency " do you mean interdependency? for example if combobox1 changes then combobox2 changes and vice versa?
Could you upload a sample workbook (without sensitive data) to a sharing site like dropbox.com or google drive?
And then share the link here.
Click to expand...
Hello Akuini,

Yes, Its actually interdependency what I meant. Unfortunately I am not able to Upload the file as its quite weighty, however if it is of any use, you would find attached the image of my combobox, where I want each combobox to hold unique elements from a table called "DynamicPath_2" in a sheet called "MDB" and they all are supposed to be interdependent except "N° Maco" which is a textbox populated based on the value of "Name of Project" combobox. below is the code I have gathered and put in place so far.

Dim vList
Dim d As Object

'sheet's name where the list is located.
Private Const sList As String = "MDB"
'Table name where the list is located
Private Const tbl As String = "DynamicPath_2"



Private Sub UserForm_Initialize()

vList = Sheets(sList).ListObjects("DynamicPath_2").DataBodyRange.Columns("A:M")
Set d = CreateObject("scripting.dictionary")
d.CompareMode = vbTextCompare

End Sub

Private Sub ComboBox1_DropButtonClick()
Dim i As Long
d.RemoveAll
For i = LBound(vList) To UBound(vList)
If UCase(vList(i, 3)) = UCase(ComboBox2.Value) Then d(vList(i, 1)) = Empty

Next
ComboBox1.List = d.keys
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, 3)) = Empty

Next
ComboBox2.List = d.keys

End Sub

Private Sub ComboBox2_Change()
Dim myRange As Range, f As Range

Set myRange = Worksheets("MDB").Range("B:C")

Set f = myRange.Find(What:=ComboBox2.Value, LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False) '<--| try and find combobox selected value
If f Is Nothing Then
TextBox1.Value = ""
Else '<--| ... otherwise...
TextBox1.Value = f.Offset(, -1)

End If
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, 3)) = UCase(ComboBox2.Value) Then d(vList(i, 4)) = Empty

Next
ComboBox3.List = d.keys
End Sub

In the above code, I have managed to make combobox2 and combobox3 dependent on the value of combobox1. However I couldn't manage to accomplish the interdependency.

Thank you really for your help... You are also welcome to change the codes completely if required.
 
Upvote 0
SamirBhowmik said:
Hello Akuini,

Yes, Its actually interdependency what I meant. Unfortunately I am not able to Upload the file as its quite weighty, however if it is of any use, you would find attached the image of my combobox, where I want each combobox to hold unique elements from a table called "DynamicPath_2" in a sheet called "MDB" and they all are supposed to be interdependent except "N° Maco" which is a textbox populated based on the value of "Name of Project" combobox. below is the code I have gathered and put in place so far.

Dim vList
Dim d As Object

'sheet's name where the list is located.
Private Const sList As String = "MDB"
'Table name where the list is located
Private Const tbl As String = "DynamicPath_2"



Private Sub UserForm_Initialize()

vList = Sheets(sList).ListObjects("DynamicPath_2").DataBodyRange.Columns("A:M")
Set d = CreateObject("scripting.dictionary")
d.CompareMode = vbTextCompare

End Sub

Private Sub ComboBox1_DropButtonClick()
Dim i As Long
d.RemoveAll
For i = LBound(vList) To UBound(vList)
If UCase(vList(i, 3)) = UCase(ComboBox2.Value) Then d(vList(i, 1)) = Empty

Next
ComboBox1.List = d.keys
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, 3)) = Empty

Next
ComboBox2.List = d.keys

End Sub

Private Sub ComboBox2_Change()
Dim myRange As Range, f As Range

Set myRange = Worksheets("MDB").Range("B:C")

Set f = myRange.Find(What:=ComboBox2.Value, LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False) '<--| try and find combobox selected value
If f Is Nothing Then
TextBox1.Value = ""
Else '<--| ... otherwise...
TextBox1.Value = f.Offset(, -1)

End If
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, 3)) = UCase(ComboBox2.Value) Then d(vList(i, 4)) = Empty

Next
ComboBox3.List = d.keys
End Sub

In the above code, I have managed to make combobox2 and combobox3 dependent on the value of combobox1. However I couldn't manage to accomplish the interdependency.

Thank you really for your help... You are also welcome to change the codes completely if required.
Click to expand...
 

Attachments

  • VBA screen.JPG
    VBA screen.JPG
    49.9 KB · Views: 26
Upvote 0
Unfortunately I am not able to Upload the file as its quite weighty
Click to expand...
Can you cut down your file? just keep the userform & sheet "MDB" & about 100 rows of data in it. I need to understand your data,.

where I want each combobox to hold unique elements from a table called "DynamicPath_2"
Click to expand...
Do you mean all column has unique data? or all columns has duplicate but you want to show only unique data in the combobox?
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
3x dependent drop-downs
Replies
0
Views
190
JohnBi
J
P
  • Question
ListBox Click to textbox & delete row
Replies
0
Views
333
pure vito
P
J
  • Question
Help with VBA Code to to unlock and lock sheet when using a command a command
Replies
1
Views
134
Jeffrey Mahoney
Jeffrey Mahoney
O
  • Question
VBA - If then statement within For Next is not working
Replies
3
Views
244
Fluff
Fluff
P
  • Solved
Why to textbox can not search Uppercase
Replies
2
Views
246
Pisitju
P

Forum statistics

Threads
1,215,092
Messages
6,123,063
Members
449,090
Latest member
fragment

Share this page

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
Back
Top