| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
| Code to conditional format all black after date specified in code? | Excel Discussion (Misc queries) | |||
| Drop Down/List w/Code and Definition, only code entered when selec | Excel Worksheet Functions | |||
| stubborn Excel crash when editing code with code, one solution | Excel Programming | |||
| VBA code delete code but ask for password and unlock VBA protection | Excel Programming | |||
Hybrid Mode
