Have userform paste data to same row it was initalized from

[Progrower]

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.

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.

 

[CharlesH]

Hi,

If the userform is populated by the last row in the Initialize sheet then you need to set "Public" variable for the Row where the data is. In a module Declare a variable like:
"Public Myrow as Long" and in the code that is set for the userform Initialization set "Myrow" to equal the row number that the userform looked at. Now in the code you provide you need to change the portion of the code that place the data to the worksheet "lRow" to "Myrow".

 

[Norie]

Eric

How is the userform 'initialized'?

Don't you use the row number somewhere when you are doing that?

 

[Progrower]

I use this code to check for the word YES in column T
FrmGrowInfo.Show False

Private Sub Worksheet_Change(ByVal Target As Range)


Dim i As Long
For i = 63 To 4 Step -1
    If Range("T" & i).Value = "YES" Then
'Sub Msgbox_Yes_No()
      Dim Response As Integer

      ' Displays a message box with the yes and no options.
      Response = MsgBox(prompt:="Would You Like to Enter the Info For" & vbCrLf & "The Other Growers Product On The Cart" & vbCrLf & "In Row " & i & "?", Buttons:=vbYesNo)

      ' If statement to check if the yes button was selected.
      If Response = vbYes Then

        'Shows Sigle Ticket Printing User Form
        FrmGrowInfo.Show False

      Else
         ' The no button was selected.
         MsgBox "Don't Forget To Enter The Info Before Printing."
      Range("T" & i).ClearContents
      End If
            If Range("C" & i) > 0 And Range("B" & i) = "" Then
            MsgBox "Row " & i & " Needs Load Type On Count Sheet #1."
            Range("B" & i).Select
            Exit Sub
        End If

End If

Next i
End Sub

 

[Norie]

Eric

Is that code meant to check if a value in column T has been changed?

Or is it meant to be triggered by a change anywhere on the worksheet and then check what's in column T?

 

[Progrower]

If the user selects YES from the validation in column T, then a message box opens asking them if they
want to enter the grower info "yes or no". If they select yes the userform opens. If they select no the
the message box clears the YES out of the row that popped it.

 

[Progrower]

I'm sorry but I'm still not getting anywhere with this. I tried setting Public Myrow as Long but that errors out every time. I'm now trying to get the Myrow to return the correct row value but it just keeps showing a value = to 0 when I hold my mouse over it. I'm not sure how to get the Myrow to pick up the row that triggered the userform.

 

[xenou]

When the userform initializes:

myRow = ActiveCell.Row

 

[Progrower]

Thanks Xenou
I amended the code with you submit-ion and it works!

Private Sub cmdAddGrower1_Click()
Dim lRow As Long
Dim lGrow As Long
Dim lProd As Long
Dim ws As Worksheet
Dim Myrow As Long

Set ws = Worksheets("Other Growers on Cart")

Myrow = ActiveCell.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
  .Range("A" & Myrow).Value = Me.CboGrower1.Value
  .Range("B" & Myrow).Value = Me.CboGrower1Product.Value
  .Range("C" & Myrow).Value = Me.txtGrower1ProductAmount.Value
' Grower #2
  .Range("D" & Myrow).Value = Me.CboGrower2.Value
  .Range("E" & Myrow).Value = Me.CboGrower2Product.Value
  .Range("F" & Myrow).Value = Me.TxtGrower2ProductAmount.Value
' Grower #3
  .Range("G" & Myrow).Value = Me.CboGrower3.Value
  .Range("H" & Myrow).Value = Me.CboGrower3Product.Value
  .Range("I" & Myrow).Value = Me.txtGrower3ProductAmount.Value
' Grower #4
  .Range("J" & Myrow).Value = Me.CboGrower4.Value
  .Range("K" & Myrow).Value = Me.CboGrower4Product.Value
  .Range("L" & Myrow).Value = Me.txtGrower4ProductAmount.Value
' Grower #5
  .Range("M" & Myrow).Value = Me.CboGrower5.Value
  .Range("N" & Myrow).Value = Me.CboGrower5Product.Value
  .Range("O" & Myrow).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

Again Thank you very much.

 

[Norie]

Eric

I was going to suggest adding an hidden textbox to the form which you could populate with the row before you show it.

You could get the row from the code that runs before you show it.

Looks like xenou beat me to it though.