title: Next Record Button With a Twist
body: <p>Long time user, first time Inquirer here. <strong>Brand-new</strong> to VBA and struggling a bit. I have reviewed and tried suggestions in each post dedicated to Next record button functionality in Excel VBA and cannot seem to find a solution that works for my situation. </p>
  
  <p>I have created a user form intended to assist users in reviewing very wide data in an Excel spreadsheet (a sample of the worksheet can be provided, if needed/helpful). Due to user requirements, the form needs to launch <strong>when an entire row is selected</strong> (this is the twist). I have managed to get the majority of controls to behave as expected, with the exception of the Next and Previous buttons. My trouble seems to be in obtaining 'txt.rownum' (Target.Row) from the Worksheet > SelectionChange routine to the Buttons. Here is a graphic of the form:</p>
  
  <p><a href="https://i.stack.imgur.com/CbeuR.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/CbeuR.png" alt="enter image description here"></a></p>
  
  <p>In the worksheet routine, the form captures the current row as <code>UserForm1.txt_rownum = Target.Row</code>. Here is the first part of the code that confirms a whole row has been selected and populates the form:</p>
  
  <pre><code>    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Not Intersect(Target, Range("A2:CE1000")) Is Nothing Then
      If Target.Address = Target.EntireRow.Address Then
          'yes, full row is selected
          UserForm1.txt_prop_name = Cells(Target.Row, 1)
          UserForm1.txt_alt_prop_name = Cells(Target.Row, 2)
          UserForm1.txt_client_prop_code = Cells(Target.Row, 3)
          UserForm1.txt_rownum = Target.Row
          UserForm1.txt_mailability_score = Cells(Target.Row, 4)
          UserForm1.txt_ad_descr = Cells(Target.Row, 5)...
  </code></pre>
  
  <p>My current code behind the Next button looks like this:</p>
  
  <pre><code>Dim currentrow As Long
  
  'Next button
  Private Sub CommandButton2_Click()
  Dim lastrow As Long
  lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
  
  currentrow = currentrow + 1
  
  UserForm1.txt_prop_name = Cells(currentrow, 1)
  UserForm1.txt_alt_prop_name = Cells(currentrow, 2)
  UserForm1.txt_client_prop_code = Cells(currentrow, 3)
  
  End Sub
  </code></pre>
  
  <p>The form correctly indexes down one row at a time when the Next button is clicked, but it starts at <strong>A1</strong>, and not at the current row that the form is displaying. How do I pass txt.rownum (or Target.Row) to the Next button, so that it will properly move to the next record down from the current row <em>and</em> display the resulting next row of data from the spreadsheet?</p>
  
  <p>Thanks in advance for your assistance and patience - much appreciated!</p>