Hmm, that would only work if theye filled them in in order and never chnged their minds though.

How about something like this:

Have subs like this in the combobox enter events:

Private Sub ComboBox1_Enter()
    AddItems 1
End Sub

Private Sub ComboBox2_Enter()
    AddItems 2
End Sub
And add the following sub to the form code module:


Sub AddItems(n As Integer)
Dim strOld As String
    Dim i As Long
    Dim blAdd As Boolean
    Dim r As Range
'store currently selected and clear combo
    With Me.Controls("ComboBox" & n)
    strOld = .Text
    .Clear
    End With
'find the items to display
    For Each r In Sheets("Data").Range("teamdata")
    blAdd = True
    For i = 1 To 12
        If i <> n Then
            If Me.Controls("ComboBox" & i).Text = r.Value Then
                blAdd = False
            End If
        End If
    Next I
    If blAdd Then Me.Controls("ComboBox" & n).AddItem r.Value
Next
'now reselect the previous item
    For i = 0 To (Me.Controls("ComboBox" & n).ListCount - 1)
    If Me.Controls("ComboBox" & n).List(i) = strOld Then
        Me.Controls("Combobox" & n).ListIndex = I
        Exit For
    End If
    Next i
End Sub