Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
No Duplicates Allowed
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
No Duplicates Allowed
'-- 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
No Duplicates Allowed
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.txtordernum ber.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 wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
No Duplicates Allowed
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
No Duplicates Allowed
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!!!) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
No Duplicates Allowed
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!! "Jim Cone" wrote: 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!!!) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
allowed/taken | Excel Worksheet Functions | |||
allowed / taken | Excel Worksheet Functions | |||
MODIFICATION IS NOT ALLOWED | Excel Worksheet Functions | |||
parameters not allowed | Excel Discussion (Misc queries) | |||
Are the macros allowed? | Excel Programming |