samirbhowmik
11-02-2021, 08:16 AM
https://www.ozgrid.com/forum/index.php?thread/1230599-making-listboxes-interdependent/&postID=1253463#post1253463
https://www.mrexcel.com/board/…ent.1185098/#post-5780796 (https://www.mrexcel.com/board/threads/creating-listboxes-which-are-interdependent.1185098/#post-5780796)
Hello,
I have an userform with many listboxes which retrieve the unique values from a big table which a master data table. In the userform I have multiple Listboxes which I would like make as interdependent as in when one listbox value is selected the other listboxes values get filtered automatically. Below I have a piece of code which does the work for the values selected in listbox 1 to automatically filter out contents in listbox 2
Private Sub ListBox1_Change()
Dim a() As Variant
Dim dic As Object
Dim i As Long, j As Long
Set dic = CreateObject("Scripting.Dictionary")
a = Sheets("MDB").Range("A2", Sheets("MDB").Range("C" & Rows.Count).End(3)).Value
ListBox2.Clear
With ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
For j = 1 To UBound(a, 1)
If a(j, 1) = .List(i) Then
dic(a(j, 3)) = Empty
End If
Next
End If
Next
If dic.Count > 0 Then ListBox2.List = Application.Transpose(dic.keys)
End With
End Sub
I have attached a sample file with data of 3 months for a test. Also, a screenshot of the Userform.
Thank you in advance to whomever who would render a helping hand to fix this for me.29129
https://www.mrexcel.com/board/…ent.1185098/#post-5780796 (https://www.mrexcel.com/board/threads/creating-listboxes-which-are-interdependent.1185098/#post-5780796)
Hello,
I have an userform with many listboxes which retrieve the unique values from a big table which a master data table. In the userform I have multiple Listboxes which I would like make as interdependent as in when one listbox value is selected the other listboxes values get filtered automatically. Below I have a piece of code which does the work for the values selected in listbox 1 to automatically filter out contents in listbox 2
Private Sub ListBox1_Change()
Dim a() As Variant
Dim dic As Object
Dim i As Long, j As Long
Set dic = CreateObject("Scripting.Dictionary")
a = Sheets("MDB").Range("A2", Sheets("MDB").Range("C" & Rows.Count).End(3)).Value
ListBox2.Clear
With ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
For j = 1 To UBound(a, 1)
If a(j, 1) = .List(i) Then
dic(a(j, 3)) = Empty
End If
Next
End If
Next
If dic.Count > 0 Then ListBox2.List = Application.Transpose(dic.keys)
End With
End Sub
I have attached a sample file with data of 3 months for a test. Also, a screenshot of the Userform.
Thank you in advance to whomever who would render a helping hand to fix this for me.29129