SamirBhowmik
New Member
- Joined
- Sep 23, 2021
- Messages
- 26
- Office Version
- 365
- 2013
- Platform
- Windows
Hello,
I have around 10 listboxes placed in an userform where the data in each listbox are inter-related. (a screenshot of the userform is attached). I would like to link all the listboxes as in any change in any listbox should only keep the relevant data in the other listboxes. I have managed to do the same with listbox1 and listbox2 where changes in listbox1 would keep the corresponding data in listbox2 with the help of @DanteAmor. below is the code:
What I want to achieve is that for example any change in listbox2 or listbox3, listbox4 etc. will keep only the corresponding data in all the other listboxes and all the listboxes should have this feature
I am quite new to vba and with the help of this community I will be able to learn it well.
Thank you
I have around 10 listboxes placed in an userform where the data in each listbox are inter-related. (a screenshot of the userform is attached). I would like to link all the listboxes as in any change in any listbox should only keep the relevant data in the other listboxes. I have managed to do the same with listbox1 and listbox2 where changes in listbox1 would keep the corresponding data in listbox2 with the help of @DanteAmor. below is the code:
VBA Code:
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("M" & 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
What I want to achieve is that for example any change in listbox2 or listbox3, listbox4 etc. will keep only the corresponding data in all the other listboxes and all the listboxes should have this feature
I am quite new to vba and with the help of this community I will be able to learn it well.
Thank you