How to perform validation on multiple combo boxes?

[Svetlin Panov]

Hi Guys,

I have a form with 48 Combo Boxes on it and I am filling them from the same range.
I want to remove the values that are already selected not be available in the next ComboBox.
I have tried many ways to make it work but i couldn't. Probably the problem is because I initialize all the ComboBox values in the beginning and if i want to remove a value i need some kind of event.
Here is my Intialization code:

Public Sub UserForm_Initialize()
Dim cLoc As Range
Dim ws As Worksheet
Dim combo As String
Dim i As Integer
 
For i = 1 To 48
    combo = "ComboBox" & CStr(i) '48 ComboBoxes on the Form
    Set ws = Worksheets("Calc")
        For Each cLoc In ws.Range("Stops") 'Range to fill the ComboBoxes values
          With Me.Controls(combo)
            .AddItem cLoc.Value
            .List(.ListCount - 1, 1) = cLoc.Offset(0, 1).Value 'Adding second column
          End With
        Next cLoc
Next i
 
End Sub
 

Thanks in Advance.