I have a userform paste data to the first empty row in a sheet. That user form is initalized from a condition on a different sheet. How can I pass the row number that initalized the form so that it will paste the data on the same row in the second sheet?
Here's what I'm currently using.
Thanks for looking.
Here's what I'm currently using.
Code:
Private Sub cmdAddGrower1_Click()
Dim lRow As Long
Dim lGrow As Long
Dim lProd As Long
Dim ws As Worksheet
Set ws = Worksheets("Other Growers on Cart")
'find first empty row in database
lRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
lGrow = Me.CboGrower1.ListIndex
lProd = Me.CboGrower1Product.ListIndex
'check for a entry
If Trim(Me.CboGrower1.Value) = "" Then
Me.CboGrower1.SetFocus
MsgBox "Please enter a Name for Grower #1"
Exit Sub
End If
If Trim(Me.CboGrower1Product.Value) = "" Then
Me.CboGrower1Product.SetFocus
MsgBox "Please enter a Product for Grower #1"
Exit Sub
End If
If Trim(Me.txtGrower1ProductAmount.Value) = "" Then
Me.txtGrower1ProductAmount.SetFocus
MsgBox "Please enter a Product Amount for Grower #1"
Exit Sub
End If
'copy the data to the database
With ws
' Grower #1
.Cells(lRow, 1).Value = Me.CboGrower1.Value
.Cells(lRow, 2).Value = Me.CboGrower1Product.Value
.Cells(lRow, 3).Value = Me.txtGrower1ProductAmount.Value
' Grower #2
.Cells(lRow, 4).Value = Me.CboGrower2.Value
.Cells(lRow, 5).Value = Me.CboGrower2Product.Value
.Cells(lRow, 6).Value = Me.TxtGrower2ProductAmount.Value
' Grower #3
.Cells(lRow, 7).Value = Me.CboGrower3.Value
.Cells(lRow, 8).Value = Me.CboGrower3Product.Value
.Cells(lRow, 9).Value = Me.txtGrower3ProductAmount.Value
' Grower #4
.Cells(lRow, 10).Value = Me.CboGrower4.Value
.Cells(lRow, 11).Value = Me.CboGrower4Product.Value
.Cells(lRow, 12).Value = Me.txtGrower4ProductAmount.Value
' Grower #5
.Cells(lRow, 13).Value = Me.CboGrower5.Value
.Cells(lRow, 14).Value = Me.CboGrower5Product.Value
.Cells(lRow, 15).Value = Me.txtGrower5ProductAmount.Value
End With
'clear the data
Me.CboGrower1.Value = ""
Me.CboGrower1Product.Value = ""
Me.txtGrower1ProductAmount.Value = ""
Me.CboGrower2.Value = ""
Me.CboGrower2Product.Value = ""
Me.TxtGrower2ProductAmount.Value = ""
Me.CboGrower3.Value = ""
Me.CboGrower3Product.Value = ""
Me.txtGrower3ProductAmount.Value = ""
Me.CboGrower4.Value = ""
Me.CboGrower4Product.Value = ""
Me.txtGrower4ProductAmount.Value = ""
Me.CboGrower5.Value = ""
Me.CboGrower5Product.Value = ""
Me.txtGrower5ProductAmount.Value = ""
Me.CboGrower1.SetFocus
End Sub
Thanks for looking.