Find previous from last selected

[Subbie]

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:

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:

'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:

Any help appreciated.
Subbie

 

[dmt32]

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

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

 

[Subbie]

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:

Sub Navigate(ByVal Direction As XlSearchDirection)

Have tried to figure it out without success. Any thoughts?
Regards
Subbie

 

[dmt32]

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

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

 

[Subbie]

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

 

[dmt32]

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

 

[dmt32]

Hi,
think may have found conflict

try using updated 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

 

[Subbie]

Dave your a genius!

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

Much appreciated
Subbie

 

[dmt32]

Dave your a genius!

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

Much appreciated
Subbie

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