Prevent users from entering Duplicate Data

[kiwikiki718]

I created a VBA code that will prevent users from selecting duplicate data from a drop down list in column C of my excel spreadsheet, however I want users to be able to select "Other" in multiple fields without receiving an error.

Here is the code that I have written but need to show how to exclude the selection "Other".

Private Sub worksheet_change(ByVal Target As Range)
If Application.CountIf(Range("C:C"), Target) > 1 Then
MsgBox "Duplate Custom field Selected!", vbCritical, "Remove Data"
Target.Value = ""
End If
End Sub

thank you in advance

 

[dreid1011]

Perhaps this?

Private Sub worksheet_change(ByVal Target As Range)
If Application.CountIf(Range("C:C"), Target) > 1 Then
If Target.Value <> "Other" Then
MsgBox "Duplate Custom field Selected!", vbCritical, "Remove Data"
Target.Value = ""
End If
End If
End Sub

 

[kiwikiki718]

Perhaps this?

Private Sub worksheet_change(ByVal Target As Range)
If Application.CountIf(Range("C:C"), Target) > 1 Then
If Target.Value <> "Other" Then
MsgBox "Duplate Custom field Selected!", vbCritical, "Remove Data"
Target.Value = ""
End If
End If
End Sub

Great, this worked! Thank You!

 

[dreid1011]

You're welcome.