Userform combobox to stop if duplicate (cobobox text)

[Sabotage]

Hello Wizards,

I thank you for any future help in advance. I have a userform with multiple comboboxes and some of them are used for the same purpose. By that I mean these comboboxes use the same dynamical named range as rowsource. I want to stop the user from duplicating a combobox twice, so I'm looking to loop through each comboboxes with the same name prefix (like: cb_01, cb_02...) and if the value/text of the active combobox already used in another, it will give an warning message and clear the active cbox. I'm thinking to put the procedure onto the exit event because I dont want it to check whilst typing into the active combobox. I hope it makes sense.

Please help me...

Thanks

 

[Yongle]

Modify to suit your own needs

Private Sub cb1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Call Dupes
End Sub

Private Sub cb2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Call Dupes
End Sub
Private Sub cb3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Call Dupes
End Sub

Private Sub Dupes()
    Dim arr As Variant, cb As Variant, cBox As Variant
    arr = Array(cb1, cb2, cb3)
    For Each cb In arr
        For Each cBox In arr
            If cb.Name <> cBox.Name Then
                If cb <> "" And cb.Value = cBox.Value Then
                    MsgBox cb.Value & vbCr & "deleted", vbCritical, "Duplicates not permitted"
                    cBox.Value = ""
                    Exit Sub
                End If
            End If
        Next cBox
    Next cb
End Sub

 

[Sabotage]

Yongle,

Dunno why but I could not reply to your solution multiple times. I hope this will work now with quoting you. Your code works like a charm! Thank you ever so much!

Modify to suit your own needs

Private Sub cb1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Call Dupes
End Sub

Private Sub cb2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Call Dupes
End Sub
Private Sub cb3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Call Dupes
End Sub

Private Sub Dupes()
    Dim arr As Variant, cb As Variant, cBox As Variant
    arr = Array(cb1, cb2, cb3)
    For Each cb In arr
        For Each cBox In arr
            If cb.Name <> cBox.Name Then
                If cb <> "" And cb.Value = cBox.Value Then
                    MsgBox cb.Value & vbCr & "deleted", vbCritical, "Duplicates not permitted"
                    cBox.Value = ""
                    Exit Sub
                End If
            End If
        Next cBox
    Next cb
End Sub