VBA code to prevent the same NAME being chosen in two separate ComboBoxes

[leopardhawk]

I have a couple of UserForms with ComboBoxes that are used to select NAMES from a list. Is there a way to prevent the same NAME from being chosen, and perhaps throw up a MsgBox with an error telling the user to choose another name from the list??

It's the UserForm called by the 'Partial Shift Filled' button on Sheet1. Example workbook attached, code below.

Thanks!

Private Sub CancelCommandButton_Click()

Unload Me

End Sub

Private Sub ClearCommandButton_Click()

Call UserForm_Initialize

End Sub

Private Sub OKCommandButton_Click()
        
    Dim dFIND As Range, FirstFound As String, r As Long
        
    With Sheets(2)
        
        Set dFIND = .Range("A:A").Find(Format(ShiftDTPicker.Value, "DD/MM/YYYY"), _
                                       LookIn:=xlValues, _
                                       LookAt:=xlWhole, _
                                       SearchOrder:=xlByRows, _
                                       SearchDirection:=xlNext, _
                                       MatchCase:=False)
        
        If Not dFIND Is Nothing Then
            FirstFound = dFIND.Address
            Do
                If dFIND.Offset(, 8) = AbsentComboBox.Value Then
                    r = dFIND.Row
                    Exit Do
                End If
                Set dFIND = .Range("A:A").FindNext(After:=dFIND)
            Loop While dFIND.Address <> FirstFound
        End If
        
        If r > 0 Then
            'Both matched in row r
            'Export Data to worksheet
            .Cells(r, "G").Value = WorkingComboBox.Value
            .Cells(r, "H").Value = WPCComboBox.Value
        Else
            'No double match
            MsgBox "No matched pair found. ", vbExclamation, "No Match"
        End If
        
    End With
    
End Sub

Private Sub UserForm_Initialize()

'Default Date to Todays Date
ShiftDTPicker.Value = Date

'Empty WorkingComboBox
WorkingComboBox.Clear

'Fill WorkingComboBox
With WorkingComboBox
    .AddItem "Addison"
    .AddItem "Anderson"
    .AddItem "Brown"
    .AddItem "Buhr"
    .AddItem "Campbell"
    .AddItem "Chapin"
    .AddItem "Cinquemani"
    .AddItem "Coombs"
    .AddItem "Deo"
    .AddItem "DeRusha"
    .AddItem "Ditching"
    .AddItem "Elwood"
    .AddItem "Forster"
    .AddItem "Giesbrecht"
    .AddItem "Gill"
    .AddItem "Gladwell"
    .AddItem "Gridnyev"
    .AddItem "Grivtsov"
    .AddItem "Harding"
    .AddItem "Heron"
    .AddItem "Hughes"
    .AddItem "Kelemen"
    .AddItem "Kendirli"
    .AddItem "Kisselev"
    .AddItem "Larson"
    .AddItem "Lord"
    .AddItem "Lulka"
    .AddItem "Macdonald"
    .AddItem "MacDonald"
    .AddItem "MacMath"
    .AddItem "Marshall"
    .AddItem "McIntosh"
    .AddItem "McKenzie"
    .AddItem "McKiel"
    .AddItem "Mills"
    .AddItem "Montgomerie"
    .AddItem "Morritt"
    .AddItem "Nehustan"
    .AddItem "Nissen"
    .AddItem "Pannier"
    .AddItem "Parikh"
    .AddItem "Penland"
    .AddItem "Pietrzak"
    .AddItem "Pollard"
    .AddItem "Pratt"
    .AddItem "Richmond"
    .AddItem "Rowe"
    .AddItem "Sargent"
    .AddItem "Scott"
    .AddItem "Smurzynski"
    .AddItem "Steacy"
    .AddItem "Stewart"
    .AddItem "Vecchiola"
    .AddItem "Warnock"
    .AddItem "White"
    .AddItem "Young"
End With

'Empty WPCComboBox
WPCComboBox.Clear
' Me is optional
Range("A1").Value = UCase(Me.WPCComboBox.Text)

'Fill WPCComboBox
With WPCComboBox
    .AddItem "MUP"
    .AddItem "OT"
    .AddItem "PROMO"
    .AddItem "REG"
    .AddItem "ROT"
    .AddItem "TOP"
End With

'Empty AbsentComboBox
AbsentComboBox.Clear

'Fill AbsentComboBox
With AbsentComboBox
    .AddItem "Addison"
    .AddItem "Anderson"
    .AddItem "Brown"
    .AddItem "Buhr"
    .AddItem "Campbell"
    .AddItem "Chapin"
    .AddItem "Cinquemani"
    .AddItem "Coombs"
    .AddItem "Deo"
    .AddItem "DeRusha"
    .AddItem "Ditching"
    .AddItem "Elwood"
    .AddItem "Forster"
    .AddItem "Giesbrecht"
    .AddItem "Gill"
    .AddItem "Gladwell"
    .AddItem "Gridnyev"
    .AddItem "Grivtsov"
    .AddItem "Harding"
    .AddItem "Heron"
    .AddItem "Hughes"
    .AddItem "Kelemen"
    .AddItem "Kendirli"
    .AddItem "Kisselev"
    .AddItem "Larson"
    .AddItem "Lord"
    .AddItem "Lulka"
    .AddItem "Macdonald"
    .AddItem "MacDonald"
    .AddItem "MacMath"
    .AddItem "Marshall"
    .AddItem "McIntosh"
    .AddItem "McKenzie"
    .AddItem "McKiel"
    .AddItem "Mills"
    .AddItem "Montgomerie"
    .AddItem "Morritt"
    .AddItem "Nehustan"
    .AddItem "Nissen"
    .AddItem "Pannier"
    .AddItem "Parikh"
    .AddItem "Penland"
    .AddItem "Pietrzak"
    .AddItem "Pollard"
    .AddItem "Pratt"
    .AddItem "Richmond"
    .AddItem "Rowe"
    .AddItem "Sargent"
    .AddItem "Scott"
    .AddItem "Smurzynski"
    .AddItem "Steacy"
    .AddItem "Stewart"
    .AddItem "Vecchiola"
    .AddItem "Warnock"
    .AddItem "White"
    .AddItem "Young"
End With

'Set Focus on ShiftDTPicker
ShiftDTPicker.SetFocus

End Sub
 
Private Sub WorkingComboBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If WorkingComboBox.ListIndex < 0 Then
        MsgBox "You must select an employee from the list!", vbCritical
        Cancel = True
        WorkingComboBox.DropDown
    End If
End Sub

Private Sub AbsentComboBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If AbsentComboBox.ListIndex < 0 Then
        MsgBox "You must select an employee from the list!", vbCritical
        Cancel = True
        AbsentComboBox.DropDown
    End If
End Sub