No Duplicates Allowed

[D]

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

 

[Jim Cone]

'--
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.Value)), 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

 

[Dave Peterson]

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

if application.countif(ws.range("a:a"),me.txtordernumber.value) > 0 then
'it's already there
'msgbox and beep???
else
'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 = ""
end if

Me.txtOrderStatus.SetFocus

End Sub

untested, uncompiled--watch for typos.

 

[D]

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]

I would have to know the error in order to have a thought..
Also, are you sure that the Msgbox line is the bad line?
In any case, I would try Dave's solution first.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"D"
wrote in message

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!!!)

 

[D]

Thank you for your help. I did get the code to work. Seems there was some
issue with the spaces in the the front of the line and gave me a syntax
error.

Thanks Again!!