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.
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
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,