Find previous from last selected

S

Subbie

New Member
Joined
May 11, 2019
Messages
32
Hi all
Can someone help me with the following issue:
I have written a successful routine on the userform to find the last entry. I want then to go to the previous record until the user finds the one he/she wants and also be able to return to previous. I have separate buttons for previous and next but the find last will not conect with them:
The findlast code that works is:
Code: 
Private Sub CMDLast_Click()
        With Application
             .ScreenUpdating = False
             .EnableEvents = False
        End With
Dim ws As Worksheet
Dim i As Long


Set ws = ThisWorkbook.Sheets("MasterTransactions")


        With ws
            i = .Rows.Count
            lstdt = .Range("A" & i).End(xlUp).value
            lstdt1 = .Range("B" & i).End(xlUp).value
            lstdt2 = .Range("C" & i).End(xlUp).value
            lstdt3 = .Range("D" & i).End(xlUp).value
            lstdt4 = .Range("E" & i).End(xlUp).value
            lstdt5 = .Range("F" & i).End(xlUp).value
            lstdt6 = .Range("G" & i).End(xlUp).value
            
                  Me.DateInput = lstdt
                  Me.UserPayeeNm = lstdt1
                  Me.UserPayeeGrp = lstdt2
                  Me.Ammt = lstdt3
                  Me.Method = lstdt4
                  Me.ChqRefNo = lstdt5
                  Me.EntryType = lstdt6
       End With
       
  With Application
             .ScreenUpdating = True
             .EnableEvents = True
  End With
End Sub
Below are the previous and next codes that work, but when I select the find last the codes take me to the first record and then scroll from the beginning of the records:

Code: 
'Next record code
Private Sub CMDNext_Click()
       With Application
             .ScreenUpdating = False
             .EnableEvents = False
        End With
Dim LastRow As Long
LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).row
        If currentrow = LastRow Then
                MsgBox "you are in the last row! No more data."
                Exit Sub
        End If
currentrow = currentrow + 1
          DateInput = Cells(currentrow, 1)
          UserPayeeNm = Cells(currentrow, 2)
          UserPayeeGrp = Cells(currentrow, 3)
          Ammt = Cells(currentrow, 4)
          Method = Cells(currentrow, 5)
          ChqRefNo = Cells(currentrow, 6)
          EntryType = Cells(currentrow, 7)
          
        With Application
                     .ScreenUpdating = True
                     .EnableEvents = True
        End With
End Sub
'Next record code end


'Previous record code


Private Sub CMDPrev_Click()
        With Application
             .ScreenUpdating = False
             .EnableEvents = False
        End With
        
            If currentrow = 2 Then
                    MsgBox "You are in the first row!"
                    Exit Sub
            End If
            currentrow = currentrow - 1
            
                    DateInput = Cells(currentrow, 1)
                    UserPayeeNm = Cells(currentrow, 2)
                    UserPayeeGrp = Cells(currentrow, 3)
                    Ammt = Cells(currentrow, 4)
                    Method = Cells(currentrow, 5)
                    ChqRefNo = Cells(currentrow, 6)
                    EntryType = Cells(currentrow, 7)
                    
With Application
             .ScreenUpdating = True
             .EnableEvents = True
End With
                        
End Sub
'Previous record code end




'Update record code


Private Sub cmdupdate_Click()
Dim Dname As Date, Uname As String, Ugroup As String, AM As Currency, Md As String, CHqnm As String, Entype As String
            Dname = DateInput
                Cells(currentrow, 1) = DateInput
            Uname = UserPayeeNm
                Cells(currentrow, 2) = UserPayeeNm
            Ugroup = UserPayeeGrp
                Cells(currentrow, 3) = UserPayeeGrp
            AM = Ammt
                Cells(currentrow, 4) = Ammt
                'Format Input for currency and numbers
                    Range("D2:D10000").Select
                    With Selection
                        Ammt = Format(Ammt, "#,##0.00")
                        .value = .value
                    End With
            Md = Method
                Cells(currentrow, 5) = Method
            CHqnm = ChqRefNo
                Cells(currentrow, 6) = ChqRefNo
            Entype = EntryType
                Cells(currentrow, 7) = EntryType
End Sub
'Update record code end
So to be clear what I am trying to achieve is that when the 'Find Last Record Code' shows the last record, the previous and next buttons will allow movement through the records FROM the Last Found Record. I tried a 'GoToRecord' button but it doesn't work either.
Here is an image of the user form:
userform.jpg


Any help appreciated.
Subbie
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
Hi,
rather than repeating a lot of your code in each navigation button you would find it easier to call one common code.


Make a backup of your workbook & replace all your navigation code with following

Rich (BB code): 
Sub Navigate(ByVal Direction As XlSearchDirection)
    Dim LastRow As Long
    Const StartRow As Long = 2
    Static RecordRow As Long
    Dim sh As Worksheet
    
    Set sh = Worksheets("Sheet1")
    
    LastRow = sh.Cells(sh.Rows.Count, 1).End(xlUp).Row
    
    Select Case Direction
        
    Case xlFirst
        RecordRow = StartRow
    Case xlNext
        RecordRow = RecordRow + 1
    Case xlPrevious
        RecordRow = RecordRow - 1
    Case xlLastCell
        RecordRow = LastRow
    End Select
    
    RecordRow = IIf(RecordRow < StartRow, StartRow, _
    IIf(RecordRow > LastRow, LastRow, RecordRow))
    
    With Me
        .DateInput = sh.Cells(RecordRow, 1).Text
        .UserPayeeNm = sh.Cells(RecordRow, 2)
        .UserPayeeGrp = sh.Cells(RecordRow, 3)
        .Ammt = sh.Cells(RecordRow, 4)
        .Method = sh.Cells(RecordRow, 5)
        .ChqRefNo = sh.Cells(RecordRow, 6)
        .EntryType = sh.Cells(RecordRow, 7)
        .DateInput.SetFocus
        
        With .cmdNext
            .Enabled = CBool(RecordRow < LastRow)
            Me.cmdLast.Enabled = .Enabled
        End With
        .cmdPrevious.Enabled = CBool(RecordRow > StartRow)
    End With
    
End Sub




Private Sub cmdLast_Click()
    Navigate xlLastCell
End Sub


Private Sub cmdNext_Click()
    Navigate xlNext
End Sub


Private Sub cmdPrevious_Click()
  Navigate xlPrevious
End Sub




Private Sub UserForm_Initialize()
    Navigate xlFirst
End Sub

Change sheet name shown in red as required

Dave
 
Last edited:
Upvote 0
Hi Dave
I removed all my navigation routines and installed yours.
The routine throws an error:
'Member already exists in an object module from which the object module derives'
At this point:
Code: 
Sub Navigate(ByVal Direction As XlSearchDirection)

Have tried to figure it out without success. Any thoughts?
Regards
Subbie
 
Upvote 0
Interesting suggests a name conflict although code has worked many times in other projects


I neglected to look at your update code so delete ALL code & replace with following

Code: 
Dim sh As Worksheet
Dim RecordRow As Long
Private Sub cmdupdate_Click()
    Dim ControlsArray As Variant, ControlName As Variant
    Dim ColIndex As Integer
    Const MyDateFormat As String = "##/##/####"
    
    ControlsArray = Array(DateInput, UserPayeeNm, UserPayeeGrp, Ammt, Method, ChqRefNo, EntryType)
    
    ColIndex = 1
    RecordRow = Val(Me.Tag)
    
    For Each ControlName In ControlsArray
        With sh.Cells(RecordRow, ColIndex)
            If ControlName.Text Like MyDateFormat Then
                .Value = DateValue(ControlName.Text)
            ElseIf ControlName.Name = Ammt.Name Then
                .Value = Val(Ammt)
                .NumberFormat = "#,##0.00"
            Else
                .Value = ControlName.Text
            End If
        End With
        ColIndex = ColIndex + 1
    Next ControlName
        
    MsgBox "Record Updated", 48, "Updated"
End Sub


'Update record code end
Sub Navigate(ByVal NavigateDirection As XlSearchDirection)
    Dim LastRow As Long
    Const StartRow As Long = 2
    
    LastRow = sh.Cells(sh.Rows.Count, 1).End(xlUp).Row
    
    Select Case NavigateDirection
        
    Case xlFirst
        RecordRow = StartRow
    Case xlNext
        RecordRow = RecordRow + 1
    Case xlPrevious
        RecordRow = RecordRow - 1
    Case xlLastCell
        RecordRow = LastRow
    End Select
    
    RecordRow = IIf(RecordRow < StartRow, StartRow, _
                IIf(RecordRow > LastRow, LastRow, RecordRow))
    
    With Me
        .DateInput = sh.Cells(RecordRow, 1).Text
        .UserPayeeNm = sh.Cells(RecordRow, 2)
        .UserPayeeGrp = sh.Cells(RecordRow, 3)
        .Ammt = sh.Cells(RecordRow, 4)
        .Method = sh.Cells(RecordRow, 5)
        .ChqRefNo = sh.Cells(RecordRow, 6)
        .EntryType = sh.Cells(RecordRow, 7)
        .DateInput.SetFocus
        
        With .cmdNext
            .Enabled = CBool(RecordRow < LastRow)
            Me.cmdLast.Enabled = .Enabled
        End With
        .cmdPrevious.Enabled = CBool(RecordRow > StartRow)
        .Tag = RecordRow
    End With
    
End Sub




Private Sub cmdLast_Click()
    Navigate xlLastCell
End Sub


Private Sub cmdNext_Click()
    Navigate xlNext
End Sub


Private Sub cmdPrevious_Click()
  Navigate xlPrevious
End Sub


Private Sub UserForm_Initialize()
    Set sh = Worksheets("Sheet1")
    Navigate xlFirst
End Sub

Note two variables at top of code - These MUST be placed at very TOP of your forms code page OUTSIDE any procedure.
and see if this resolves

Dave
 
Last edited:
Upvote 0
Hi Dave
have removed Update code and placed the global variables outside the code. Have looked for conflicts and cannot find any variable or name that looks like it is clashing. But still the same error.

Regards
Subbie
 
Upvote 0
Just created mock-up workbook & all fine - can you place copy of your workbook with sample data in a dropbox & provide link to it here?

Dave
 
Upvote 0
Hi,
think may have found conflict

try using updated code

Code: 
Dim sh As Worksheet
Dim RecordRow As Long
Private Sub cmdupdate_Click()
    Dim ControlsArray As Variant, ControlName As Variant
    Dim ColIndex As Integer
    Const MyDateFormat As String = "##/##/####"
    
    ControlsArray = Array(DateInput, UserPayeeNm, UserPayeeGrp, Ammt, Method, ChqRefNo, EntryType)
    
    ColIndex = 1
    RecordRow = Val(Me.Tag)
    
    For Each ControlName In ControlsArray
        With sh.Cells(RecordRow, ColIndex)
            If ControlName.Text Like MyDateFormat Then
                .Value = DateValue(ControlName.Text)
            ElseIf ControlName.Name = Ammt.Name Then
                .Value = Val(Ammt)
                .NumberFormat = "#,##0.00"
            Else
                .Value = ControlName.Text
            End If
        End With
        ColIndex = ColIndex + 1
    Next ControlName
        
    MsgBox "Record Updated", 48, "Updated"
End Sub






'Update record code end
Sub GetRecord(ByVal Direction As XlSearchDirection)
    Dim LastRow As Long
    Const StartRow As Long = 2
    
    LastRow = sh.Cells(sh.Rows.Count, 1).End(xlUp).Row
    
    Select Case Direction
        
    Case xlFirst
        RecordRow = StartRow
    Case xlNext
        RecordRow = RecordRow + 1
    Case xlPrevious
        RecordRow = RecordRow - 1
    Case xlLastCell
        RecordRow = LastRow
    End Select
    
    RecordRow = IIf(RecordRow < StartRow, StartRow, _
                IIf(RecordRow > LastRow, LastRow, RecordRow))
    
    With Me
        .DateInput = sh.Cells(RecordRow, 1).Text
        .UserPayeeNm = sh.Cells(RecordRow, 2)
        .UserPayeeGrp = sh.Cells(RecordRow, 3)
        .Ammt = sh.Cells(RecordRow, 4)
        .Method = sh.Cells(RecordRow, 5)
        .ChqRefNo = sh.Cells(RecordRow, 6)
        .EntryType = sh.Cells(RecordRow, 7)
        .DateInput.SetFocus
        
        With .cmdNext
            .Enabled = CBool(RecordRow < LastRow)
            Me.cmdLast.Enabled = .Enabled
        End With
        .cmdPrevious.Enabled = CBool(RecordRow > StartRow)
        .Tag = RecordRow
    End With
    
End Sub




Private Sub cmdLast_Click()
    GetRecord xlLastCell
End Sub


Private Sub cmdNext_Click()
    GetRecord xlNext
End Sub


Private Sub cmdPrevious_Click()
  GetRecord xlPrevious
End Sub


Private Sub UserForm_Initialize()
    Set sh = Worksheets("Sheet1")
    GetRecord xlFirst
End Sub
 
Upvote 0
Dave your a genius!

My sincere thanks for your help.
The code works perfectly.

Much appreciated
Subbie
 
Upvote 0
Subbie said:
Dave your a genius!

My sincere thanks for your help.
The code works perfectly.

Much appreciated
Subbie
Click to expand...

Curious worked in original version on one system but not the other & will have to experiment further at some stage but glad all now working for you.

Many thanks for feedback

Dave
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

theYaniac
  • Solved
I would like to insert a blank row between each new unique identifier in column A.
Replies
3
Views
155
MARK858
MARK858
D
  • Solved
Sequential Numbering of New Records using VBA
2
Replies
10
Views
1K
dmt32
D
E
  • Question
Trying to use a month name in my VLookup
Replies
2
Views
139
Eric Penfold
E
L
  • Question
Combining two codes by Using a Messagebox like Form with yes/no or alike
Replies
1
Views
101
lojanica
L
S
  • Solved
Tweak Macro that Imports specific table columns from a different workbook to filter a column first
Replies
4
Views
158
sspatriots
S

Forum statistics

Threads
1,214,646
Messages
6,120,720
Members
448,986
Latest member
andreguerra

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top