Previous/Next buttona in Userform

[Spotniq]

Greetings!

I've been searching all over the net for what initially seemed a simple piece of code to find however, I've found nothing that helps on this subject. I have a a very simple form that users use to input data into Sheet 2. I want to build in the ability for them to use a Previous/Next button to scroll through the records they have typed in and make changes to the records if they so desire. It would also be nice to include a mssg box once the user has reached the very first or last record statting "first/last record has been reached". The spreadsheet is built in such a way that once the user is done and submits the form it closes out, sends a copy of the spreadsheet via email, and deletes the data and goes back to a blank template. Any help on this would be VERY much appreciated as like I've stated I've been searching for days with no answers. Thank you in advance!!

 

[dmt32]

Hi,
Following is just a suggested approach you could take to do what you want but you will need to adjust / adapt as required to meet your specific project need. Code assumes that your data starts in row 1 of your spreadsheet.

Previous & Next buttons will be enabled / disabled when they reach start / end of the data range.

Place all code in your forms code page.

Dim Data As Variant
Dim LastRow As Long
Dim r As Long


'===========================================


Private Sub UserForm_Initialize()


'worksheet with your data
'change name as required
    With Sheets("Data")
        'find last row in data range
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        
        'pass values to array in the data range
        'adjust range as required
        Data = .Range("A1:C" & LastRow).Value


    End With
    
    'start at first record
    r = xlFirst
    
    'or from activecell
    'r = ActiveCell.Row - 1
    
    
    RangeRow Direction:=r


End Sub


'=================================================


Private Sub NextRecord_Click()
    RangeRow Direction:=xlNext
End Sub


'=================================================


Private Sub PrevRecord_Click()
    RangeRow Direction:=xlPrevious
End Sub
'=================================================


Sub RangeRow(ByVal Direction As Long)
    
    'set value of r
    r = IIf(Direction = xlPrevious, r - 1, r + 1)
    
    'ensure value of r stays within data range
    If r < 1 Then r = 1
    If r > LastRow Then r = LastRow
    
    'fill textboxes on userform
    'from Data Array
    With Me
        .TextBox1.Text = Data(r, 1)
        .TextBox2.Text = Data(r, 2)
        .TextBox3.Text = Data(r, 3)
        
        'set enabled status of next previous buttons
        .NextRecord.Enabled = r < LastRow
        .PrevRecord.Enabled = r > 1
    End With
End Sub

Hope Helpful

Dave

 

[Spotniq]

Thank you Dmt32! I will def. Give it a try and report back! Thanks again for taking the time to write that out!

 

[Spotniq]

Hi Dave,

so I've set up the code as you directed. I set up the Dims as the first 3 lines of code in my userform code window. I then proceeded to paste UserForm_initialize under said sub (obviously renaming the sheet and putting in my own range). I did the same with the next and previous button (renamed the sub to NextRecord & PrevRecord). Where I ran into an error was with the last bit of code Sub RangeRow (ByVal Direction As Long). I get "Compile Error: Method or data member not found" and it highlights the .NextRecord.Enabled = r < LastRow. Do you know what may be causing this?

 

[mikerickson]

I prefer using multi column ListBoxes to show select from lots of data

The following code goes in a user form's code module.


The function DataRange should be altered to the sheet and number of columns that you use in your situation
The Initialize event shows how the ListBox is set-up
Both the butNext and butPrevious routines Beep when the user "turns the corner" from the highest data row to the first (or vice versa)
The function RangePointedTo returns the row in DataRange that corresponds to the row selected in the ListBox.

Since my demo scenario has three columns, I added three text boxes to edit those columns. The code driving that is at the end. (My formulation does not alter the worksheet unless a button is pressed. I like the user to have the option to press a Cancel button that doesn't change the worksheet. Personal Preference.)

Function DataRange() As Range
    Rem as written, this returns three columns, deteremined by column A
    Rem adjust as needed
    With Sheet1.Range("A:A")
        Set DataRange = Range(.Cells(1, 3), .Cells(.Rows.Count, 1).End(xlUp))
    End With
End Function

Private Sub ListBox1_Change()
    With ListBox1
        TextBox1.Text = .List(.ListIndex, 0)
        TextBox2.Text = .List(.ListIndex, 1)
        TextBox3.Text = .List(.ListIndex, 2)
    End With
End Sub

Private Sub UserForm_Initialize()
    With DataRange
        ListBox1.ColumnCount = .Columns.Count
        ListBox1.List = .Value
    End With
    ListBox1.ListIndex = 0
End Sub

Private Sub butNext_Click()
    With ListBox1
        .ListIndex = (.ListIndex + 1) Mod (.ListCount)
        If .ListIndex = 0 Then Beep
    End With
End Sub

Private Sub butPrevious_Click()
    With ListBox1
        If .ListIndex = 0 Then Beep
        .ListIndex = (.ListIndex + .ListCount - 1) Mod (.ListCount)
    End With
End Sub

Function RangePointedTo() As Variant
    Set RangePointedTo = DataRange.Rows(1).Offset(ListBox1.ListIndex, 0)
End Function

Private Sub ListBox1_Change()
    With ListBox1
        TextBox1.Text = .List(.ListIndex, 0)
        TextBox2.Text = .List(.ListIndex, 1)
        TextBox3.Text = .List(.ListIndex, 2)
    End With
End Sub
Private Sub TextBox1_Change()
    With ListBox1
        .List(.ListIndex, 0) = TextBox1.Text
    End With
End Sub
Private Sub TextBox2_Change()
    With ListBox1
        .List(.ListIndex, 1) = TextBox1.Text
    End With
End Sub
Private Sub TextBox3_Change()
    With ListBox1
        .List(.ListIndex, 2) = TextBox1.Text
    End With
End Sub

 

[Spotniq]

Hi Mike,

So this form stores data that the user inputs however once the spreadsheet closes all the data is deleted. Will the list box method work in this case?

 

[dmt32]

Hi,
Code should work - Post the changes you have made.

Dave

 

[mikerickson]

My approach assumes that the data for the user form comes from a worksheet.
It does not concern itself with Saving the workbook, that is outside the user form.
A Write to Sheet button should be written, but isn't in the code I posted. (Its outside the scope of the For/Next question.)

 

[Spotniq]

Good morning,

Im interested in learning both aproaches as I see myself using both within my work place. Are we allowed to upload the spreadsheet on this forum so that other users can take a look at where the code is erroring out? May be easier? Thank you so much for your help gentlemen!!

 

[Spotniq]

Mike, on your method if the user is actively adding new records how does the userform/listbox update to show the newly added records? Or does this require a doffer enr piece of code?