Next Record Button With a Twist

Question

Long time user, first time Inquirer here. Brand-new 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.

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 when an entire row is selected (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:

In the worksheet routine, the form captures the current row as UserForm1.txt_rownum = Target.Row. Here is the first part of the code that confirms a whole row has been selected and populates the form:

    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)...

My current code behind the Next button looks like this:

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

The form correctly indexes down one row at a time when the Next button is clicked, but it starts at A1, 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 and display the resulting next row of data from the spreadsheet?

Thanks in advance for your assistance and patience - much appreciated!

`currentrow = UserForm1.txt_rownum + 1`? Or maybe just change the "Next" button to simply be `Rows(UserForm1.txt_rownum + 1).Select` (which will, I think, fire the `Worksheet_SelectionChange` event and therefore populate everything)? — YowE3K, Jan 15 '18 at 23:56
You might be better off using a label instead of a textbox for the row number (or at least disable the textbox) — Tim Williams, Jan 16 '18 at 00:36
It's good practice to use an instance of a UserForm as a blueprint for one's forms, e.g. via `Dim myForm As UserForm1` and `Set myForm = New UserForm1`. If instead you refer directly to the Userform you are addressing a special class basically meant to dispose as prototype. You find different approaches to handle modeless UFs (allowing actions within the app) at https://stackoverflow.com/questions/47357708/vba-destroy-a-modeless-userform-instance-properly. This could introduce you to class programming, but includes also an example for *worksheet events* (@Excelosaurus) you could adapt. — T.M., Jan 16 '18 at 10:07

score 2 · Answer 1 · answered Jan 16 '18 at 01:29

Some code recommendations:

Put the code that populations in row into a function with no arguments.
The row number will be based on a public variable. Public RowNumber as Integer
In Selection Changed, assign the value of RowNumber and call the function
In the Next Button, RowNumber = RowNumber + 1, then call the same function

This way, you are no longer duplicating the code across 2 functions.

Public RowNumber as Integer

Sub PopulateForm()

    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)...

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("A2:CE1000")) Is Nothing Then // Whatever This Is?
        If Target.Address = Target.EntireRow.Address Then


            RowNumber = Target.Row
            Call PopulateForm
        End If
  End If
End Sub

Private Sub CommandButton2_Click()

    RowNumber = RowNumber + 1
    Call PopulateForm
End Sub

Next Record Button With a Twist

1 Answers1