Making listboxes interdependent

[samirbhowmik]

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

[p45cal]

What version of Excel is this going to be used on? (I see you have Excel 2013, Office 365 and Excel 2016 depending on which forum you look)
What's the ultimate aim with this? I see the Filter selection button (but no code for it); will it just filter the table on the MDB sheet? If that's true, then from Excel 2016 onwards you can use slicers directly on that table.
In the attached are some slicers added to the MDB sheet. No VBA needed.
You can select multiple items within each slicer (either by using the usual key combinations of Ctrl + Click of Shift + Click, or by clicking on the multiselect icon at the top left of each slicer), other slicers will change to reflect available choices left. You can choose within each slicer whether to hide unavailable options or just colour them differently. Available options float to the top. You can clear filters on each slicer (again top left).
I've added a button to clear all filters from the table (which does require VBA).

[samirbhowmik]

What version of Excel is this going to be used on? (I see you have Excel 2013, Office 365 and Excel 2016 depending on which forum you look)
What's the ultimate aim with this? I see the Filter selection button (but no code for it); will it just filter the table on the MDB sheet? If that's true, then from Excel 2016 onwards you can use slicers directly on that table.
In the attached are some slicers added to the MDB sheet. No VBA needed.
You can select multiple items within each slicer (either by using the usual key combinations of Ctrl + Click of Shift + Click, or by clicking on the multiselect icon at the top left of each slicer), other slicers will change to reflect available choices left. You can choose within each slicer whether to hide unavailable options or just colour them differently. Available options float to the top. You can clear filters on each slicer (again top left).
I've added a button to clear all filters from the table (which does require VBA).


Hello p45cal,

Thank you for your idea of implementing slicers. I will work on it. The version of Excel that I am using is 2016.

[kadrl]

I had created a template about dependent list boxes. It may be useful for you.

29151

Source (https://eksi30.com/excel-dependent-list-boxes/)