prevent duplicate entries from userform

[jtodd]

Hi All
How can I prevent users from entering duplicate data in a userform.
I have tried the data validation but this only seems to work if you type in the cell an not when the data is passed from the userform.
I have the following code for the form , row 5 "seal" is the column that cannot have duplicates in.
Any help will be appreciated .

Dim lRow As Long
Dim ws As Worksheet
Set ws = Worksheets("GCNSData")
lRow = ws.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Row
With ws
.Cells(lRow, 3).Value = Me.DelDate.Value
.Cells(lRow, 2).Value = Me.OrderNum.Value
.Cells(lRow, 4).Value = Me.trailer.Value
.Cells(lRow, 5).Value = Me.seal.Value
.Cells(lRow, 6).Value = Me.DesDate.Value
.Cells(lRow, 7).Value = Me.Branch.Value
.Cells(lRow, 8).Value = Me.Palls.Value
.Cells(lRow, 9).Value = Me.loose.Value
End With
Me.DelDate.Value = ""
Me.OrderNum.Value = ""
Me.trailer.Value = ""
Me.seal.Value = ""
Me.DesDate.Value = ""
Me.Branch.Value = ""
Me.Palls.Value = ""
Me.loose.Value = ""


End Sub

 

[steve the fish]

See if this works:

Dim lRow As Long, ws As Worksheet, c As Range

Set ws = Worksheets("GCNSData")
lRow = ws.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Row

With ws.Range(ws.Cells(1, 5), ws.Cells(lr, 5))
    Set c = .Find(Me.seal.Value, LookIn:=xlValues, LookAt:=xlWhole)
End With

If c Is Nothing Then
    With ws
        .Cells(lRow, 3).Value = Me.DelDate.Value
        .Cells(lRow, 2).Value = Me.OrderNum.Value
        .Cells(lRow, 4).Value = Me.trailer.Value
        .Cells(lRow, 5).Value = Me.seal.Value
        .Cells(lRow, 6).Value = Me.DesDate.Value
        .Cells(lRow, 7).Value = Me.Branch.Value
        .Cells(lRow, 8).Value = Me.Palls.Value
        .Cells(lRow, 9).Value = Me.loose.Value
    End With
    
    Me.DelDate.Value = ""
    Me.OrderNum.Value = ""
    Me.trailer.Value = ""
    Me.seal.Value = ""
    Me.DesDate.Value = ""
    Me.Branch.Value = ""
    Me.Palls.Value = ""
    Me.loose.Value = ""
Else
    MsgBox Me.seal.Value & " is already in use"
    Me.seal.SetFocus
End If

 

[NMa91]

Alternatively Try this:

Dim lRow As Long
Dim ws As Worksheet

Set ws = Worksheets("GCNSData")

lRow = ws.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Row
 
If WorksheetFunction.CountIf(ws.Range("E:E"), Me.seal.Value) > 0 Then
   MsgBox "Duplicate seal"
   Exit Sub
End If

With ws
   .Cells(lRow, 3).Value = Me.DelDate.Value
   .Cells(lRow, 2).Value = Me.OrderNum.Value
   .Cells(lRow, 4).Value = Me.trailer.Value
   .Cells(lRow, 5).Value = Me.seal.Value
   .Cells(lRow, 6).Value = Me.DesDate.Value
   .Cells(lRow, 7).Value = Me.Branch.Value
   .Cells(lRow, 8).Value = Me.Palls.Value
   .Cells(lRow, 9).Value = Me.loose.Value
End With
Me.DelDate.Value = ""
Me.OrderNum.Value = ""
Me.trailer.Value = ""
Me.seal.Value = ""
Me.DesDate.Value = ""
Me.Branch.Value = ""
Me.Palls.Value = ""
Me.loose.Value = ""

 

[jtodd]

Thanks for the quick reply.

Sorry but I get an error on "With ws.Range(ws.Cells(1, 5), ws.Cells(lr, 5))"

 

[steve the fish]

Sorry my mistake

With ws.Range(ws.Cells(1, 5), ws.Cells(lRow, 5))

 

[jtodd]

Thanks -Works well
But ( there's allways a but) can I put an error message in to tell the user it has not been added and if so where in the code would I put it.