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