Thank You very much. Place your code into my sheet and I am having one
problem:

It doesn't like the "MsgBox "found in row " & vStatus" portion.

Any thoughts?

(By the way, you are going to make me look like a hero!!!)

"Jim Cone" wrote:


'--
Private Sub CmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim vStatus As Variant
Dim rng As Range
Set ws = Worksheets("MasterList")

'Find first empty row in MasterList
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
Set rng = ws.Range(ws.Cells(1, 2), ws.Cells(iRow, 2))

'Check for a Order number
If Trim(Me.txtOrderNumber.Value) = "" Then
Me.txtOrderNumber.SetFocus
MsgBox "Please enter the Order Number"
Exit Sub
End If

vStatus = Application.Match(CDbl(Trim(Me.txtOrderNumber.Valu e)), rng, 0)
If IsError(vStatus) Then
'copy the data to MasterList
ws.Cells(iRow, 1).Value = Me.txtOrderStatus.Value
ws.Cells(iRow, 2).Value = Me.txtOrderNumber.Value
ws.Cells(iRow, 5).Value = Me.txtOrderDate.Value
Else
MsgBox "found in row " & vStatus
End If

'Clear the form
Me.txtOrderStatus.Value = ""
Me.txtOrderNumber.Value = ""
Me.txtOrderDate.Value = ""

Me.txtOrderStatus.SetFocus
'Me.Hide
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)





"D"
wrote in message
I have a Userform which updates a worksheet with the following code. I would
like it to check to see if the txtOrderNumber has already been entered in the
MasterList before copying the data to the MasterList with a message that lets
the user know that the Order Number has already been used.

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("MasterList")
'Find first empty row in MasterList
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
'Check for a Order number
If Trim(Me.txtOrderNumber.Value) = "" Then
Me.txtOrderNumber.SetFocus
MsgBox "Please enter the Order Number"
Exit Sub
End If
'copy the data to MasterList
ws.Cells(iRow, 1).Value = Me.txtOrderStatus.Value
ws.Cells(iRow, 2).Value = Me.txtOrderNumber.Value
ws.Cells(iRow, 5).Value = Me.txtOrderDate.Value
'Clear the form
Me.txtOrderStatus.Value = ""
Me.txtOrderNumber.Value = ""
Me.txtOrderDate.Value = ""
Me.txtOrderStatus.SetFocus
End Sub