|
Code Help
This code works great except if the user is trying to change the last row of
data it will not allow the user to change. If you look down my code to *********** I thought i could put <= LastRow then but when i do this crazy things start happening to the database. Any ideas? Thank you. Jennifer Private Sub PutData() Dim r As Long If IsNumeric(RowNumber.Text) Then r = CLng(RowNumber.Text) Else MsgBox "Illegal row number" Exit Sub End If If r = 2 Then With Worksheets("ProduceData") .Cells(r, 1) = "1" .Cells(r, 2) = txtInvoice.Value .Cells(r, 11) = txtFrt.Value .Cells(r, 3) = txtDate.Value .Cells(r, 4) = cboVend.Text .Cells(r, 5) = cboRan.Value .Cells(r, 7) = txtPallet.Value .Cells(r, 8) = txtQty.Value .Cells(r, 9) = txtQtySold.Value .Cells(r, 10) = txtPrice.Value .Cells(r, 13) = txtRepakHrs.Value .Cells(r, 14) = txtRepakQty.Value DisableSave End With Else If r 1 And r < LastRow Then ****************** With Worksheets("ProduceData") .Cells(r, 1) = "=R[-1]C+1" .Cells(r, 2) = txtInvoice.Value .Cells(r, 11) = txtFrt.Value .Cells(r, 3) = txtDate.Value .Cells(r, 4) = cboVend.Text .Cells(r, 5) = cboRan.Value .Cells(r, 7) = txtPallet.Value .Cells(r, 8) = txtQty.Value .Cells(r, 9) = txtQtySold.Value .Cells(r, 10) = txtPrice.Value .Cells(r, 13) = txtRepakHrs.Value .Cells(r, 14) = txtRepakQty.Value DisableSave End With Else MsgBox "Invalid row number" End If 'select a produce item via button ActiveSheet.Unprotect If Me.OptSnap.Value Then Cells(r, 6).Value = Me.OptSnap.Caption Else Cells(r, 6).Value = Me.OptSno.Caption End If End If ActiveSheet.Protect End Sub -- Though daily learning, I LOVE EXCEL! Jennifer |
Code Help
Hi,
I tried your code - only setting Cells(r,1) - and did not get any error, even with 'r<=lastrow". You say crazy thing happens ... but what exactly? What is value of Lastrow? The value of cells(r,1) appears to equal r-1; if this is so, I offer the following code to try (at your risk!): Private Sub PutData() Dim r As Long If Not IsNumeric(rowNumber.Text) Then MsgBox "Illegal row number" Exit Sub Else r = CLng(rowNumber.Text) If r < 2 Or r Lastrow Then MsgBox "Invalid row number" Else With Worksheets("ProduceData") .Cells(r, 1) = r - 1 .Cells(r, 2) = txtInvoice.Value .Cells(r, 11) = txtFrt.Value .Cells(r, 3) = txtDate.Value .Cells(r, 4) = cboVend.Text .Cells(r, 5) = cboRan.Value .Cells(r, 7) = txtPallet.Value .Cells(r, 8) = txtQty.Value .Cells(r, 9) = txtQtySold.Value .Cells(r, 10) = txtPrice.Value .Cells(r, 13) = txtRepakHrs.Value .Cells(r, 14) = txtRepakQty.Value DisableSave End With End If ' select a produce item via button ActiveSheet.Unprotect If Me.OptSnap.Value Then Cells(r, 6).Value = Me.OptSnap.Caption Else Cells(r, 6).Value = Me.OptSno.Caption End If End If ' ActiveSheet.Protect End Sub "Jennifer" wrote: This code works great except if the user is trying to change the last row of data it will not allow the user to change. If you look down my code to *********** I thought i could put <= LastRow then but when i do this crazy things start happening to the database. Any ideas? Thank you. Jennifer Private Sub PutData() Dim r As Long If IsNumeric(RowNumber.Text) Then r = CLng(RowNumber.Text) Else MsgBox "Illegal row number" Exit Sub End If If r = 2 Then With Worksheets("ProduceData") .Cells(r, 1) = "1" .Cells(r, 2) = txtInvoice.Value .Cells(r, 11) = txtFrt.Value .Cells(r, 3) = txtDate.Value .Cells(r, 4) = cboVend.Text .Cells(r, 5) = cboRan.Value .Cells(r, 7) = txtPallet.Value .Cells(r, 8) = txtQty.Value .Cells(r, 9) = txtQtySold.Value .Cells(r, 10) = txtPrice.Value .Cells(r, 13) = txtRepakHrs.Value .Cells(r, 14) = txtRepakQty.Value DisableSave End With Else If r 1 And r < LastRow Then ****************** With Worksheets("ProduceData") .Cells(r, 1) = "=R[-1]C+1" .Cells(r, 2) = txtInvoice.Value .Cells(r, 11) = txtFrt.Value .Cells(r, 3) = txtDate.Value .Cells(r, 4) = cboVend.Text .Cells(r, 5) = cboRan.Value .Cells(r, 7) = txtPallet.Value .Cells(r, 8) = txtQty.Value .Cells(r, 9) = txtQtySold.Value .Cells(r, 10) = txtPrice.Value .Cells(r, 13) = txtRepakHrs.Value .Cells(r, 14) = txtRepakQty.Value DisableSave End With Else MsgBox "Invalid row number" End If 'select a produce item via button ActiveSheet.Unprotect If Me.OptSnap.Value Then Cells(r, 6).Value = Me.OptSnap.Caption Else Cells(r, 6).Value = Me.OptSno.Caption End If End If ActiveSheet.Protect End Sub -- Though daily learning, I LOVE EXCEL! Jennifer |
Code Help
Thank you I will try it.
-- Though daily learning, I LOVE EXCEL! Jennifer "Toppers" wrote: Hi, I tried your code - only setting Cells(r,1) - and did not get any error, even with 'r<=lastrow". You say crazy thing happens ... but what exactly? What is value of Lastrow? The value of cells(r,1) appears to equal r-1; if this is so, I offer the following code to try (at your risk!): Private Sub PutData() Dim r As Long If Not IsNumeric(rowNumber.Text) Then MsgBox "Illegal row number" Exit Sub Else r = CLng(rowNumber.Text) If r < 2 Or r Lastrow Then MsgBox "Invalid row number" Else With Worksheets("ProduceData") .Cells(r, 1) = r - 1 .Cells(r, 2) = txtInvoice.Value .Cells(r, 11) = txtFrt.Value .Cells(r, 3) = txtDate.Value .Cells(r, 4) = cboVend.Text .Cells(r, 5) = cboRan.Value .Cells(r, 7) = txtPallet.Value .Cells(r, 8) = txtQty.Value .Cells(r, 9) = txtQtySold.Value .Cells(r, 10) = txtPrice.Value .Cells(r, 13) = txtRepakHrs.Value .Cells(r, 14) = txtRepakQty.Value DisableSave End With End If ' select a produce item via button ActiveSheet.Unprotect If Me.OptSnap.Value Then Cells(r, 6).Value = Me.OptSnap.Caption Else Cells(r, 6).Value = Me.OptSno.Caption End If End If ' ActiveSheet.Protect End Sub "Jennifer" wrote: This code works great except if the user is trying to change the last row of data it will not allow the user to change. If you look down my code to *********** I thought i could put <= LastRow then but when i do this crazy things start happening to the database. Any ideas? Thank you. Jennifer Private Sub PutData() Dim r As Long If IsNumeric(RowNumber.Text) Then r = CLng(RowNumber.Text) Else MsgBox "Illegal row number" Exit Sub End If If r = 2 Then With Worksheets("ProduceData") .Cells(r, 1) = "1" .Cells(r, 2) = txtInvoice.Value .Cells(r, 11) = txtFrt.Value .Cells(r, 3) = txtDate.Value .Cells(r, 4) = cboVend.Text .Cells(r, 5) = cboRan.Value .Cells(r, 7) = txtPallet.Value .Cells(r, 8) = txtQty.Value .Cells(r, 9) = txtQtySold.Value .Cells(r, 10) = txtPrice.Value .Cells(r, 13) = txtRepakHrs.Value .Cells(r, 14) = txtRepakQty.Value DisableSave End With Else If r 1 And r < LastRow Then ****************** With Worksheets("ProduceData") .Cells(r, 1) = "=R[-1]C+1" .Cells(r, 2) = txtInvoice.Value .Cells(r, 11) = txtFrt.Value .Cells(r, 3) = txtDate.Value .Cells(r, 4) = cboVend.Text .Cells(r, 5) = cboRan.Value .Cells(r, 7) = txtPallet.Value .Cells(r, 8) = txtQty.Value .Cells(r, 9) = txtQtySold.Value .Cells(r, 10) = txtPrice.Value .Cells(r, 13) = txtRepakHrs.Value .Cells(r, 14) = txtRepakQty.Value DisableSave End With Else MsgBox "Invalid row number" End If 'select a produce item via button ActiveSheet.Unprotect If Me.OptSnap.Value Then Cells(r, 6).Value = Me.OptSnap.Caption Else Cells(r, 6).Value = Me.OptSno.Caption End If End If ActiveSheet.Protect End Sub -- Though daily learning, I LOVE EXCEL! Jennifer |
Code Help
Hey Topper,
What is the biggest differance in the macro you gave me? It works, and it didn't screw anything up. That is always good. Thank you. -- Though daily learning, I LOVE EXCEL! Jennifer "Toppers" wrote: Hi, I tried your code - only setting Cells(r,1) - and did not get any error, even with 'r<=lastrow". You say crazy thing happens ... but what exactly? What is value of Lastrow? The value of cells(r,1) appears to equal r-1; if this is so, I offer the following code to try (at your risk!): Private Sub PutData() Dim r As Long If Not IsNumeric(rowNumber.Text) Then MsgBox "Illegal row number" Exit Sub Else r = CLng(rowNumber.Text) If r < 2 Or r Lastrow Then MsgBox "Invalid row number" Else With Worksheets("ProduceData") .Cells(r, 1) = r - 1 .Cells(r, 2) = txtInvoice.Value .Cells(r, 11) = txtFrt.Value .Cells(r, 3) = txtDate.Value .Cells(r, 4) = cboVend.Text .Cells(r, 5) = cboRan.Value .Cells(r, 7) = txtPallet.Value .Cells(r, 8) = txtQty.Value .Cells(r, 9) = txtQtySold.Value .Cells(r, 10) = txtPrice.Value .Cells(r, 13) = txtRepakHrs.Value .Cells(r, 14) = txtRepakQty.Value DisableSave End With End If ' select a produce item via button ActiveSheet.Unprotect If Me.OptSnap.Value Then Cells(r, 6).Value = Me.OptSnap.Caption Else Cells(r, 6).Value = Me.OptSno.Caption End If End If ' ActiveSheet.Protect End Sub "Jennifer" wrote: This code works great except if the user is trying to change the last row of data it will not allow the user to change. If you look down my code to *********** I thought i could put <= LastRow then but when i do this crazy things start happening to the database. Any ideas? Thank you. Jennifer Private Sub PutData() Dim r As Long If IsNumeric(RowNumber.Text) Then r = CLng(RowNumber.Text) Else MsgBox "Illegal row number" Exit Sub End If If r = 2 Then With Worksheets("ProduceData") .Cells(r, 1) = "1" .Cells(r, 2) = txtInvoice.Value .Cells(r, 11) = txtFrt.Value .Cells(r, 3) = txtDate.Value .Cells(r, 4) = cboVend.Text .Cells(r, 5) = cboRan.Value .Cells(r, 7) = txtPallet.Value .Cells(r, 8) = txtQty.Value .Cells(r, 9) = txtQtySold.Value .Cells(r, 10) = txtPrice.Value .Cells(r, 13) = txtRepakHrs.Value .Cells(r, 14) = txtRepakQty.Value DisableSave End With Else If r 1 And r < LastRow Then ****************** With Worksheets("ProduceData") .Cells(r, 1) = "=R[-1]C+1" .Cells(r, 2) = txtInvoice.Value .Cells(r, 11) = txtFrt.Value .Cells(r, 3) = txtDate.Value .Cells(r, 4) = cboVend.Text .Cells(r, 5) = cboRan.Value .Cells(r, 7) = txtPallet.Value .Cells(r, 8) = txtQty.Value .Cells(r, 9) = txtQtySold.Value .Cells(r, 10) = txtPrice.Value .Cells(r, 13) = txtRepakHrs.Value .Cells(r, 14) = txtRepakQty.Value DisableSave End With Else MsgBox "Invalid row number" End If 'select a produce item via button ActiveSheet.Unprotect If Me.OptSnap.Value Then Cells(r, 6).Value = Me.OptSnap.Caption Else Cells(r, 6).Value = Me.OptSno.Caption End If End If ActiveSheet.Protect End Sub -- Though daily learning, I LOVE EXCEL! Jennifer |
Code Help
Don't mind me, I get it! That is definately a better way to put it. Thanks!
-- Though daily learning, I LOVE EXCEL! Jennifer "Jennifer" wrote: Hey Topper, What is the biggest differance in the macro you gave me? It works, and it didn't screw anything up. That is always good. Thank you. -- Though daily learning, I LOVE EXCEL! Jennifer "Toppers" wrote: Hi, I tried your code - only setting Cells(r,1) - and did not get any error, even with 'r<=lastrow". You say crazy thing happens ... but what exactly? What is value of Lastrow? The value of cells(r,1) appears to equal r-1; if this is so, I offer the following code to try (at your risk!): Private Sub PutData() Dim r As Long If Not IsNumeric(rowNumber.Text) Then MsgBox "Illegal row number" Exit Sub Else r = CLng(rowNumber.Text) If r < 2 Or r Lastrow Then MsgBox "Invalid row number" Else With Worksheets("ProduceData") .Cells(r, 1) = r - 1 .Cells(r, 2) = txtInvoice.Value .Cells(r, 11) = txtFrt.Value .Cells(r, 3) = txtDate.Value .Cells(r, 4) = cboVend.Text .Cells(r, 5) = cboRan.Value .Cells(r, 7) = txtPallet.Value .Cells(r, 8) = txtQty.Value .Cells(r, 9) = txtQtySold.Value .Cells(r, 10) = txtPrice.Value .Cells(r, 13) = txtRepakHrs.Value .Cells(r, 14) = txtRepakQty.Value DisableSave End With End If ' select a produce item via button ActiveSheet.Unprotect If Me.OptSnap.Value Then Cells(r, 6).Value = Me.OptSnap.Caption Else Cells(r, 6).Value = Me.OptSno.Caption End If End If ' ActiveSheet.Protect End Sub "Jennifer" wrote: This code works great except if the user is trying to change the last row of data it will not allow the user to change. If you look down my code to *********** I thought i could put <= LastRow then but when i do this crazy things start happening to the database. Any ideas? Thank you. Jennifer Private Sub PutData() Dim r As Long If IsNumeric(RowNumber.Text) Then r = CLng(RowNumber.Text) Else MsgBox "Illegal row number" Exit Sub End If If r = 2 Then With Worksheets("ProduceData") .Cells(r, 1) = "1" .Cells(r, 2) = txtInvoice.Value .Cells(r, 11) = txtFrt.Value .Cells(r, 3) = txtDate.Value .Cells(r, 4) = cboVend.Text .Cells(r, 5) = cboRan.Value .Cells(r, 7) = txtPallet.Value .Cells(r, 8) = txtQty.Value .Cells(r, 9) = txtQtySold.Value .Cells(r, 10) = txtPrice.Value .Cells(r, 13) = txtRepakHrs.Value .Cells(r, 14) = txtRepakQty.Value DisableSave End With Else If r 1 And r < LastRow Then ****************** With Worksheets("ProduceData") .Cells(r, 1) = "=R[-1]C+1" .Cells(r, 2) = txtInvoice.Value .Cells(r, 11) = txtFrt.Value .Cells(r, 3) = txtDate.Value .Cells(r, 4) = cboVend.Text .Cells(r, 5) = cboRan.Value .Cells(r, 7) = txtPallet.Value .Cells(r, 8) = txtQty.Value .Cells(r, 9) = txtQtySold.Value .Cells(r, 10) = txtPrice.Value .Cells(r, 13) = txtRepakHrs.Value .Cells(r, 14) = txtRepakQty.Value DisableSave End With Else MsgBox "Invalid row number" End If 'select a produce item via button ActiveSheet.Unprotect If Me.OptSnap.Value Then Cells(r, 6).Value = Me.OptSnap.Caption Else Cells(r, 6).Value = Me.OptSno.Caption End If End If ActiveSheet.Protect End Sub -- Though daily learning, I LOVE EXCEL! Jennifer |
Code Help
Thanks for the feedback. Glad it solved your problem.
If I may make a suggestion: you could improve the error message(s) by adding the value of the variable in error e.g. MsgBox r & "is an invalid row number" HTH "Jennifer" wrote: Don't mind me, I get it! That is definately a better way to put it. Thanks! -- Though daily learning, I LOVE EXCEL! Jennifer "Jennifer" wrote: Hey Topper, What is the biggest differance in the macro you gave me? It works, and it didn't screw anything up. That is always good. Thank you. -- Though daily learning, I LOVE EXCEL! Jennifer "Toppers" wrote: Hi, I tried your code - only setting Cells(r,1) - and did not get any error, even with 'r<=lastrow". You say crazy thing happens ... but what exactly? What is value of Lastrow? The value of cells(r,1) appears to equal r-1; if this is so, I offer the following code to try (at your risk!): Private Sub PutData() Dim r As Long If Not IsNumeric(rowNumber.Text) Then MsgBox "Illegal row number" Exit Sub Else r = CLng(rowNumber.Text) If r < 2 Or r Lastrow Then MsgBox "Invalid row number" Else With Worksheets("ProduceData") .Cells(r, 1) = r - 1 .Cells(r, 2) = txtInvoice.Value .Cells(r, 11) = txtFrt.Value .Cells(r, 3) = txtDate.Value .Cells(r, 4) = cboVend.Text .Cells(r, 5) = cboRan.Value .Cells(r, 7) = txtPallet.Value .Cells(r, 8) = txtQty.Value .Cells(r, 9) = txtQtySold.Value .Cells(r, 10) = txtPrice.Value .Cells(r, 13) = txtRepakHrs.Value .Cells(r, 14) = txtRepakQty.Value DisableSave End With End If ' select a produce item via button ActiveSheet.Unprotect If Me.OptSnap.Value Then Cells(r, 6).Value = Me.OptSnap.Caption Else Cells(r, 6).Value = Me.OptSno.Caption End If End If ' ActiveSheet.Protect End Sub "Jennifer" wrote: This code works great except if the user is trying to change the last row of data it will not allow the user to change. If you look down my code to *********** I thought i could put <= LastRow then but when i do this crazy things start happening to the database. Any ideas? Thank you. Jennifer Private Sub PutData() Dim r As Long If IsNumeric(RowNumber.Text) Then r = CLng(RowNumber.Text) Else MsgBox "Illegal row number" Exit Sub End If If r = 2 Then With Worksheets("ProduceData") .Cells(r, 1) = "1" .Cells(r, 2) = txtInvoice.Value .Cells(r, 11) = txtFrt.Value .Cells(r, 3) = txtDate.Value .Cells(r, 4) = cboVend.Text .Cells(r, 5) = cboRan.Value .Cells(r, 7) = txtPallet.Value .Cells(r, 8) = txtQty.Value .Cells(r, 9) = txtQtySold.Value .Cells(r, 10) = txtPrice.Value .Cells(r, 13) = txtRepakHrs.Value .Cells(r, 14) = txtRepakQty.Value DisableSave End With Else If r 1 And r < LastRow Then ****************** With Worksheets("ProduceData") .Cells(r, 1) = "=R[-1]C+1" .Cells(r, 2) = txtInvoice.Value .Cells(r, 11) = txtFrt.Value .Cells(r, 3) = txtDate.Value .Cells(r, 4) = cboVend.Text .Cells(r, 5) = cboRan.Value .Cells(r, 7) = txtPallet.Value .Cells(r, 8) = txtQty.Value .Cells(r, 9) = txtQtySold.Value .Cells(r, 10) = txtPrice.Value .Cells(r, 13) = txtRepakHrs.Value .Cells(r, 14) = txtRepakQty.Value DisableSave End With Else MsgBox "Invalid row number" End If 'select a produce item via button ActiveSheet.Unprotect If Me.OptSnap.Value Then Cells(r, 6).Value = Me.OptSnap.Caption Else Cells(r, 6).Value = Me.OptSno.Caption End If End If ActiveSheet.Protect End Sub -- Though daily learning, I LOVE EXCEL! Jennifer |
| All times are GMT +1. The time now is 12:23 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com