Hello,

I have a form that enters data into columns A - O. Column F must be unique.
The sheet is called DataEntry and the textbox is called tSerial. I have
read previous posts regarding similiar scenarios however, I cannot seem to
get it to work. I need the Form to reject a duplicate entry in Column F
(other than blanks), so that a Msgbox appears, but the current data is not
removed or added once the Add Entry button is pressed.

Thanks in advance. . .

This might give you a start?

Private Sub tfield_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Len(Trim(tfield.Value)) 0 Or tfield.Value = vbNull Then
Dim xr As Long
For xr = 1 To Worksheets("DataEntry").Cells(Rows.Count,
"F").End(xlUp).Row
If CStr(Worksheets("DataEntry").Cells(xr, 6).Value) =
CStr(Trim(tfield.Value)) Then
MsgBox "Key field is Duplicate!"
Exit Sub
End If
Next xr
Else
MsgBox "Key field is Empty!"
End If
End Sub


--
Cheers
Nigel



"Richard" wrote in message
...
Hello,

I have a form that enters data into columns A - O. Column F must be
unique.
The sheet is called DataEntry and the textbox is called tSerial. I have
read previous posts regarding similiar scenarios however, I cannot seem to
get it to work. I need the Form to reject a duplicate entry in Column F
(other than blanks), so that a Msgbox appears, but the current data is not
removed or added once the Add Entry button is pressed.

Thanks in advance. . .

Yes, this was a perfect start. I then used the Len below as a check to
reject blank entries from being made.

Thanks

"Nigel" wrote:

This might give you a start?

Private Sub tfield_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Len(Trim(tfield.Value)) 0 Or tfield.Value = vbNull Then
Dim xr As Long
For xr = 1 To Worksheets("DataEntry").Cells(Rows.Count,
"F").End(xlUp).Row
If CStr(Worksheets("DataEntry").Cells(xr, 6).Value) =
CStr(Trim(tfield.Value)) Then
MsgBox "Key field is Duplicate!"
Exit Sub
End If
Next xr
Else
MsgBox "Key field is Empty!"
End If
End Sub


--
Cheers
Nigel



"Richard" wrote in message
...
Hello,

I have a form that enters data into columns A - O. Column F must be
unique.
The sheet is called DataEntry and the textbox is called tSerial. I have
read previous posts regarding similiar scenarios however, I cannot seem to
get it to work. I need the Form to reject a duplicate entry in Column F
(other than blanks), so that a Msgbox appears, but the current data is not
removed or added once the Add Entry button is pressed.

Thanks in advance. . .