Forum Discussion

GranSupt's avatar
GranSupt
Copper Contributor
Feb 04, 2026

VBA Code "Next" button. Should be so simple...

I have the following codes in the click event for a "next" and "prev" buttons on my userform. The prev button works fine, the next button will not advance to the next record in my table, and instead gives me the "Last record" message box (regardless of the active row). If I remove the If...Then loop it works fine. WHY is it looping to the "last record" msgbox when it is clearly not on the last record?? Any ideas greatly appreciated

 

Dim LastFind As Range

Dim CurrentRow As Long

 

Private Sub CommandButton6_Click()

 

If CurrentRow < LastRow Then

CurrentRow = CurrentRow + 1

LoadRecord CurrentRow

 

Else

MsgBox "This is the last record."

 

End If

 

End Sub

 

Private Sub CommandButton7_Click()

 

If CurrentRow > 5 Then

CurrentRow = CurrentRow - 1

LoadRecord CurrentRow

 

Else

MsgBox "This is the first record."

 

End If

End Sub

Macros and VBA

1 Reply

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Feb 05, 2026

    Declare your variables at the top of the UserForm

    Dim CurrentRow As Long   ' Keeps track of which record you are on
Dim LastRow As Long      ' Stores the last row of your data

    Initialize the UserForm

    Private Sub UserForm_Initialize()
    ' Find the last row of data in column A
    LastRow = Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row
    
    ' Start with the first record (row 5)
    CurrentRow = 5
    
    ' Load the first record into the form
    LoadRecord CurrentRow
End Sub

    Next Button (CommandButton6)

    Private Sub CommandButton6_Click()
    ' Debug: See current and last row in Immediate Window
    Debug.Print "CurrentRow=" & CurrentRow & " LastRow=" & LastRow
    
    ' Check if we are already at the last record
    If CurrentRow < LastRow Then
        ' Move to the next row
        CurrentRow = CurrentRow + 1
        
        ' Load the new record into the form
        LoadRecord CurrentRow
    Else
        ' If we are at the last record, show a message
        MsgBox "This is the last record."
    End If
End Sub

    Prev Button (CommandButton7)

    Private Sub CommandButton7_Click()
    ' Debug: See current and first row
    Debug.Print "CurrentRow=" & CurrentRow
    
    ' Check if we are already at the first record
    If CurrentRow > 5 Then
        ' Move to the previous row
        CurrentRow = CurrentRow - 1
        
        ' Load the new record into the form
        LoadRecord CurrentRow
    Else
        ' If we are at the first record, show a message
        MsgBox "This is the first record."
    End If
End Sub

    LoadRecord Subroutine

    You probably already have this, but just for clarity...

    Sub LoadRecord(RowNumber As Long)
    ' Example: fill two textboxes from columns A and B
    TextBox1.Value = Sheet1.Cells(RowNumber, "A").Value
    TextBox2.Value = Sheet1.Cells(RowNumber, "B").Value
End Sub

    UserForm opens → UserForm_Initialize sets CurrentRow = 5 and LastRow dynamically.

    Clicking Next → increases CurrentRow until LastRow.

    Clicking Prev → decreases CurrentRow until 5.

    Messages appear if you try to go past first or last record.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

Resources