Macro for data entry that checks for duplicate record

[JRW520]

Hello Forum!

I have a spreadsheet that uses a Form for Data entry. Each record has ID, Name, State, and about 20 more columns of information for each ID.

The data entry is working well.

What I need now is to add a check for a UNIQUE ID. If they are adding a new record I need my form to return a message if the ID they use is already in the database. So if they enter XXXXX and that is in the file already - they should get a message that XXXXX is already in the database and to please enter a new ID.

And once they fix XXXXX to XXXXY then the record is added.

I should add that the ID is stored in column A.

Thank you for your assistance!

 

[AlphaFrog]

Are you asking how to test if the new ID is unique? If yes, try one of these...

[color=green]'The find method[/color]
[color=darkblue]If[/color] [color=darkblue]Not[/color] Range("A:A").Find(What:="UniqueID", LookAt:=xlWhole, MatchCase:=False) [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color] MsgBox "Not Unique"

[color=green]'or CountIf function[/color]
If Application.Count[color=darkblue]If[/color](Range("A:A"), "UniqueID") > 0 [color=darkblue]Then[/color] MsgBox "Not Unique"

[color=green]'or Match Function[/color]
If IsNumeric(Application.Match("UniqueID", Range("A:A"), 0)) [color=darkblue]Then[/color] MsgBox "Not Unique"

 

[JLGWhiz]

Copy to Worksheet code module

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
If Not Intersect(Target, Range("A:A")) Is Nothing And Target <> "" Then
    NewVal = Target.Value
    Application.Undo
    If Application.CountIf(Range("A:A"), NewVal) <> 0 Then
        MsgBox "Entry Already Exists.  Verify Input", vbExclamation, "DUPLICATE ENTRY!"
        Application.EnableEvents = True
        Exit Sub
    Else
        Target = NewVal
    End If
End If
Application.EnableEvents = True
End Sub

 

[JRW520]

Yes I am looking to test new ID as being unique. You stated that better than I!

I will give all your options a try and see which works the best. Thank you!!