Unique entries in textbox

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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. . .
>
>
>