Excel VBA: Move to next row to display to textboxes

Question

i was able to do this a few years back with offset and whatever it was called. but it was close to 10 years ago and I forgot it.

So onload/initialize, i want to point to row number 2. Then the left and right button should move the pointer to the next row of data to display in the the 2 textboxes.

Please disregard commandbutton3 there. Thanks!

Here's the code that i got but it only moves 1 row.

 
Private Sub btn_Next_Click()

Dim ws1 As Worksheet
Dim CurRow As Long

Set ws1 = Sheets("TestSheet")

CurRow = 2

txtName.Text = ws1.Cells(CurRow, 1).Offset(1, 0)

txtType.Text = ws1.Cells(CurRow, 2).Offset(1, 0)

CurRow = CurRow + 1

End Sub

Answer 1

Try this mod.

Dim CurRow As Long

Private Sub UserForm_Initialize()

CurRow = 1

End Sub



Private Sub btn_Next_Click()

Dim ws1 As Worksheet
Set ws1 = Sheets("TestSheet")

CurRow = CurRow + 1
txtName.Text = ws1.Cells(CurRow, 1).Offset(1, 0)
txtType.Text = ws1.Cells(CurRow, 2).Offset(1, 0)


End Sub

One experiment for the back button

Private Sub btn_Previous_Click()   'apply the name of the button

Dim ws1 As Worksheet
Set ws1 = Sheets("TestSheet")

If CurRow > 2 then
    CurRow = CurRow - 1
    txtName.Text = ws1.Cells(CurRow, 1).Offset(1, 0)
    txtType.Text = ws1.Cells(CurRow, 2).Offset(1, 0)
End If

End Sub

Defined CurRow with module scope it will preserve its value between the calls.