Excel Userform next button go to next row cell

Question

I have a set of data in 3x columns: Name, Age, Job.

I have put together a userform where a user can use to find Names, Ages and Jobs, in 3x separate textboxes (TextBox1, TextBox2 and TextBox3). It starts right at the top, which is done by design, which works fine. I also have a 'next' button, where when a user would click next and it will go to the next item in the list.

So when the user clicks next, it should ideally go to row 3 data (row 1 = headers).

Code:

Private Sub CommandButton3_Click()
    Dim AANo As String
    Dim AANa As String
    Dim AAEm As String
    Dim NextLR As Long

    NextLR = Sheets("AASD").Cells(Rows.count, "QH").End(xlUp).row

    For i = 2 To NextLR
        With Worksheets("AASD")
            Name = .Range(i, 8).Value
            Age = .Range(i, 9).Value
            Job = .Range(i, 10).Value
        End With

        TextBox1.Value = Name
        TextBox2.Value = Age
        TextBox3.Value = Job
    Next i

End Sub

This in theory should work, but it doesn't. I am not sure where I went wrong. Any advise where I went wrong?

Thanks

Modified code:

Private Sub CommandButton3_Click()
    Dim AANo As String
    Dim AANa As String
    Dim AAEm As String
    Dim NextLR As Long
    Dim count As Long
    NextLR = Sheets("AASD").Cells(Rows.count, "QH").End(xlUp).row
    count = 2
    For i = count To NextLR
        With Worksheets("AASD")
            AANo = .Range(i, 8).Value
            AAName = .Range(i, 9).Value
            AAEm = .Range(i, 10).Value
        End With

        TextBox1.Value = AANo
        TextBox2.Value = AANa
        TextBox3.Value = AAEm
        count = count + 1
        Exit For

    Next i

End Sub

"Doesn't work" is never helpful. Your code loops through every row each time you press the button. — SJR
I have modified code, see edit. Still not sure where I am going wrong. — Oday Salim
Have you tried a global variable (k) and each time they click next, it adds 1 to k, up to the max k can be, then it resets to 3? — Cyril
Could you work with combobox/listbox instead? Just add items when you open the userform. Then play around with listindex when button is clicked. As a user it would be great to be able to select from a list instead clicking through all rows. — JvdV

Cyril Cyril · Accepted Answer · 2018-06-14T13:51:04

Elaborate on my comment, somethign like:

Public k as Long

Private Sub CommandButton3_Click()
    Dim lr as Long
    with sheets("AASD")
        lr = .cells(.rows.count,8).end(xlup).row
        If k > lr OR k <3 then k = 3
        TextBox1.Value = .Cells(k, 8).Value
        TextBox2.Value = .Cells(k, 9).Value
        TextBox3.Value = .Cells(k, 10).Value
    end with
    k = k + 1
End Sub

If you go the route of a combobox for the values, you can set the combobox array during userform_initialize() using:

dim arr() as variant, lr as long
with sheets("AASD")
    lr = .cells(.rows.count,8).end(xlup).row
    arr = .range(.cells(3,8),.cells(lr,8)).Value
    UserForm1.ComboBox1.List = arr
end with

Excel Userform next button go to next row cell

2 Answers