Using Next and Previous Buttons in VBA to Load Userform Given Data in Excel

Question

I am working on a userform in excel-VBA. I am trying to use a next and previous button to cycle through previous entries in a table. The previous button should select the previous row and load it into the userform. The next button should select the next row and load it into the userform as well.The data table has no set length. Below is my attempt at this. I know I have a disconnect between the two buttons function and am not sure how to connect them. Any help would be greatly appreiciated. I will respond as quickly as I can.

Public PrevRow1 As Long
Public cnt1 As Long
Public cnt2 As Long

'Prev Button
Private Sub CommandButton4_Click()
cnt1 = cnt1 + 1


Sheet4.Activate
PrevRow1 = WorksheetFunction.CountA(Range("A:A")) + 1 - cnt1 + cnt2
ComboBox1.Value = Cells(PrevRow1, 1)
ComboBox10.Value = Cells(PrevRow1, 2)
ComboBox2.Value = Cells(PrevRow1, 3)
ComboBox3.Value = Cells(PrevRow1, 4)
ComboBox4.Value = Cells(PrevRow1, 5)
TextBox6.Value = Cells(PrevRow1, 6)
TextBox2.Value = Cells(PrevRow1, 7)
TextBox3.Value = Cells(PrevRow1, 8)
TextBox4.Value = Cells(PrevRow1, 10)
ComboBox6.Value = Cells(PrevRow1, 11)
ComboBox7.Value = Cells(PrevRow1, 12)
ComboBox8.Value = Cells(PrevRow1, 13)
ComboBox9.Value = Cells(PrevRow1, 14)
TextBox5.Value = Cells(PrevRow1, 15)

If PrevRow1 = 1 Then
MsgBox "This is the last entry.", , "Alert!"

CommandButton4.Enabled = False
End If




Me.Label15.Caption = PrevRow1 - 1 & "  " & "of" & "  " &   WorksheetFunction.CountA(Range("A:A")) - 1

End Sub


'Next button
Private Sub CommandButton5_Click()
Dim i As Long

cnt2 = cnt2 + 1


Dim NextRow1 As Long
Sheet4.Activate
NextRow1 = PrevRow1 - (cnt1 - cnt2) + 3


ComboBox1.Value = Cells(NextRow1, 1)
ComboBox10.Value = Cells(NextRow1, 2)
ComboBox2.Value = Cells(NextRow1, 3)
ComboBox3.Value = Cells(NextRow1, 4)
ComboBox4.Value = Cells(NextRow1, 5)
TextBox6.Value = Cells(NextRow1, 6)
TextBox2.Value = Cells(NextRow1, 7)
TextBox3.Value = Cells(NextRow1, 8)
TextBox4.Value = Cells(NextRow1, 10)
ComboBox6.Value = Cells(NextRow1, 11)
ComboBox7.Value = Cells(NextRow1, 12)
ComboBox8.Value = Cells(NextRow1, 13)
ComboBox9.Value = Cells(NextRow1, 14)
TextBox5.Value = Cells(NextRow1, 15)



Me.Label15.Caption = NextRow1 - 1 & "  " & "of" & "  " & WorksheetFunction.CountA(Range("A:A")) - 1
End Sub

New Code

Public nCurrentRow As Long

Sub UserForm_Initialize()
nCurrentRow = Sheets(4).Cells(Rows.Count, 1).End(xlUp).Row
TraverseData (nCurrentRow)
end sub

'Prev button
Private Sub CommandButton14_Click()
Do
    nCurrentRow = nCurrentRow - 1
    TraverseData (nCurrentRow)
Loop Until nCurrentRow = 1 Or Sheets(4).Cells(nCurrentRow, 1).Value = Me.ComboBox12.Value
End Sub

'Next Button
Private Sub CommandButton15_Click()

Do
    nCurrentRow = nCurrentRow + 1
    TraverseData (nCurrentRow)
Loop Until Sheets(4).Cells(nCurrentRow, 1).Value = "" Or Sheets(4).Cells(nCurrentRow, 1).Value = Me.ComboBox12.Value
End Sub

Private Sub TraverseData(nRow As Long)
Me.ComboBox1.Value = Sheets(4).Cells(nRow, 1)
Me.ComboBox10.Value = Sheets(4).Cells(nRow, 2)
Me.ComboBox2.Value = Sheets(4).Cells(nRow, 3)
Me.ComboBox3.Value = Sheets(4).Cells(nRow, 4)
Me.ComboBox4.Value = Sheets(4).Cells(nRow, 5)
Me.TextBox6.Text = Sheets(4).Cells(nRow, 6)
Me.TextBox2.Text = Sheets(4).Cells(nRow, 7)
Me.TextBox3.Value = Sheets(4).Cells(nRow, 8)
Me.TextBox4.Value = Sheets(4).Cells(nRow, 10)
Me.ComboBox6.Value = Sheets(4).Cells(nRow, 11)
Me.ComboBox7.Value = Sheets(4).Cells(nRow, 12)
Me.ComboBox8.Value = Sheets(4).Cells(nRow, 13)
Me.ComboBox9.Value = Sheets(4).Cells(nRow, 14)
Me.TextBox5.Value = Sheets(4).Cells(nRow, 15)
End Sub

basodre · Accepted Answer · 2015-01-29 20:34:11Z

1

I'm having a difficult time determining what cnt1 andcnt2 refer to.

In my opinion, you only need one publicly declared variable used to reference the row of data that is loaded into the table. That variable should be set once the userform is loaded based on desired behavior. For example, the userform loads with data from the last row.

Check the below code as a basis and let me know if it helps at all:

    Public nCurrentRow As Long

Private Sub cmdNext_Click()
    Do
        nCurrentRow = nCurrentRow + 1
        TraverseData (nCurrentRow)
    Loop Until Sheet1.Cells(nCurrentRow, 1).Value = "" Or Sheet1.Cells(nCurrentRow, 1).Value = Me.ComboBox1.Value
End Sub

Private Sub cmdPrev_Click()
    Do
        nCurrentRow = nCurrentRow - 1
        TraverseData (nCurrentRow)
    Loop Until nCurrentRow = 1 Or Sheet1.Cells(nCurrentRow, 1).Value = Me.ComboBox1.Value
End Sub    
    Private Sub UserForm_Initialize()
        nCurrentRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
        TraverseData (nCurrentRow)
    End Sub

    Private Sub TraverseData(nRow As Long)
        Me.TextBox1.Value = Sheet1.Cells(nRow, 1)
        Me.TextBox2.Value = Sheet1.Cells(nRow, 2)
        Me.TextBox3.Value = Sheet1.Cells(nRow, 3)
    End Sub

Needless to say, it must be amended to handle each of your individual userform controls. Additionally, reuse your error checking to confirm that it doesn't traverse into negative rows (you've already done this in your version of the code).

Notice I encapsulated the `TraverseData into its own SubRoutine so you don't have to rewrite it for each button. It really cleans up the data.

edited Jan 29, 2015 at 20:34

answered Jan 20, 2015 at 19:41

basodre

5,7901 gold badge18 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

14 Comments

Tyler Over a year ago

I had been trying yo use the cnt variables to add counters to the buttons, but I will try your method and see if it cooperates with me haha. I'll let you know if I have any issues or if it works first try.

basodre Over a year ago

Cool. Keep us posted. Sounds to me like counters attached to the button are unnecessary.

Tyler Over a year ago

Your solution worked very well. Defenitely took out the overcomplications I made. Thank you so much!

basodre Over a year ago

Very happy to hear that. Keep us posted if you have any other issues.

Tyler Over a year ago

I've run into another issue. I want to use the Prev/Next buttons again with filtered results so that they only go between the rows that have a specific value in it. This value will be selected in a combobox elsewhere.

|

blindguy · Accepted Answer · 2015-01-20 19:44:58Z

Public ViewRow as long

'Prev Button
Private Sub CommandButton5_Click()

if ViewRow > 1 then 'assumes row 1 is the first row
    ViewRow = ViewRow - 1
    CommandButton5.Enabled = True
    if ViewRow = 1 then
        MsgBox "This is the first entry.", , "Alert!"
        CommandButton4.Enabled = False
    end if
end if


Sheet4.Activate

ComboBox1.Value = Cells(ViewRow, 1)
...
TextBox5.Value = Cells(ViewRow, 15)


Me.Label15.Caption = ViewRow & "  " & "of" & "  " & WorksheetFunction.CountA(Range("A:A"))

End Sub


'Next button
Private Sub CommandButton5_Click()
if ViewRow < WorksheetFunction.CountA(Range("A:A")) then
    ViewRow = ViewRow + 1
    CommandButton4.Enabled = True
    if ViewRow = 1 then
        MsgBox "This is the last entry.", , "Alert!"
        CommandButton5.Enabled = False
    end if
end if

Sheet4.Activate

ComboBox1.Value = Cells(NextRow1, 1)
...
TextBox5.Value = Cells(NextRow1, 15)


Me.Label15.Caption = ViewRow & "  " & "of" & "  " & WorksheetFunction.CountA(Range("A:A"))
End Sub

OR

Public ViewRow as long

'Prev Button
Private Sub CommandButton5_Click()

if ViewRow > 1 then 'assumes row 1 is the first row
    ViewRow = ViewRow - 1
    CommandButton5.Enabled = True
    if ViewRow = 1 then
        MsgBox "This is the first entry.", , "Alert!"
        CommandButton4.Enabled = False
    end if
end if

call populate_row(ViewRow)

End Sub


   'Next button
   Private Sub CommandButton5_Click()
   if ViewRow < WorksheetFunction.CountA(Range("A:A")) then
        ViewRow = ViewRow + 1
        CommandButton4.Enabled = True
        if ViewRow = 1 then
            MsgBox "This is the last entry.", , "Alert!"
            CommandButton5.Enabled = False
        end if
    end if

call populate_row(ViewRow)
End Sub

Sub populate_row(thisrow as long)
Sheet4.Activate

ComboBox1.Value = Cells(NextRow1, 1)
...
TextBox5.Value = Cells(NextRow1, 15)


    Me.Label15.Caption = ViewRow & "  " & "of" & "  " &      WorksheetFunction.CountA(Range("A:A"))
End Sub

The second option lets you make changes to how the data is added to the form without changing both functions.

Thank you for answering as well, your code helped me with a different area I was struggling with.

Collectives™ on Stack Overflow

Using Next and Previous Buttons in VBA to Load Userform Given Data in Excel

2 Answers 2

14 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

14 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related