CODEWITHSUNDEEP
excelvbabuttonuserform
Tyler
Tyler

Reputation: 45

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

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

Upvotes: 0

Views: 14003

Answers (2)

basodre
basodre

Reputation: 5770

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.

Upvotes: 1

blindguy
blindguy

Reputation: 1009

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.

Upvotes: 0

Related Questions