Following my previous post, I have managed to make a Function that populates multiple comboboxes from a same source and remove items that already exist in other ComboBoxes. Now my problem is I have to call the Function to each comboboxes which makes it redundant. I'm looking into firing the same change event to multiple comboboxes at once.
This is what my code looks like so far.
Code
Private Sub UserForm_Initialize()
Dim ctl As Control
For Each ctl In Controls
FillList ctl
Next
End Sub
Private Sub ComboBox1_Change()
Dim ctl As Control
For Each ctl In Controls
FillList ctl
Next
End Sub
Private Sub ComboBox2_Change()
Dim ctl As Control
For Each ctl In Controls
FillList ctl
Next
End Sub
Private Sub ComboBox3_Change()
Dim ctl As Control
For Each ctl In Controls
FillList ctl
Next
End Sub ' And so on
' #### Function to populate list without repeating values
'
Public Function FillList(cbx As ComboBox)
Dim myRng As Range, ctl As Control
Set myRng = ThisWorkbook.Names("petSkill").RefersToRange
Dim e, v
With myRng
v = .Value
End With
With CreateObject("scripting.dictionary")
.comparemode = 1
For Each e In v
If Not .exists(e) And e <> ComboBox1.Value _
And e <> ComboBox2.Value And e <> ComboBox3.Value _
And e <> ComboBox4.Value And e <> ComboBox5.Value _
And e <> ComboBox6.Value And e <> ComboBox7.Value _
And e <> ComboBox8.Value And e <> ComboBox9.Value _
And e <> ComboBox10.Value And e <> ComboBox11.Value _
And e <> ComboBox12.Value Then .Add e, Nothing
Next e
If .Count Then
cbx.List = Application.Transpose(.keys)
End If
End With
End Function
Display More
I've seen some approach using Collections but I can't seem to figure it out as to how should I apply the same method that suits my needs.
Any help is greatly appreciated.
Thanks and regards,