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.