Code Help

J

Jennifer

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
 
T

Toppers

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 said:
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
Click to expand...
 
J

Jennifer

Thank you I will try it.
--
Though daily learning, I LOVE EXCEL!
Jennifer


Toppers said:
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 said:
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
Click to expand...
Click to expand...
 
J

Jennifer

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 said:
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 said:
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
Click to expand...
Click to expand...
 
J

Jennifer

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
T

Toppers

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 said:
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 said:
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 said:
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

:

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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel data display userform problem 3
Hidden sheet bleeds through 3
vlookup issue 2
Help in VBA code 5
Problem with deleting blank rows or last row 4
Userforms, switching sheets 6
Please help debug this code! 1
Problems with userform, refercencing code to worksheet 2

Top