Another one.
Private Sub UserForm_Initialize()
Dim b As Variant, v As Variant
'Column B values
With Worksheets("Programmes")
b = .Range("B5", .Range("B" & Rows.Count).End(xlUp)).Value
End With
'Populate Listbox1 with unique list from column B
With CreateObject("Scripting.Dictionary")
For Each v In b
.Item(v) = 1
Next v
Me.ListBox1.List = .Keys
End With
End Sub
Private Sub CommandButton2_Click()
Dim bc As Variant, i As Long
If Me.ListBox1.ListIndex = -1 Then Exit Sub 'Nothing selected in Listbox1
'Column B:C values
With Worksheets("Programmes")
bc = .Range("B5", .Range("C" & Rows.Count).End(xlUp)).Value
End With
'Populate Listbox2 with unique list from column C of selected item from Listbox1
With CreateObject("Scripting.Dictionary")
For i = 1 To UBound(bc, 1)
If bc(i, 1) = Me.ListBox1.Value Then .Item(bc(i, 2)) = 1
Next i
Me.ListBox2.List = .Keys
End With
End Sub
Bookmarks