Modifying the "Next" button code so it works with a "Previous" button

I have a "Next" button that runs the following code. It copies the contents of the next (below) row of the source range and pastes it to a cell in the destination sheet.

I would also like to have similar code that is run by a "Previous" button which copy/pastes the contents of the previous row (above) instead.

I tried - 1 instead of + 1, but that didn't work.

Can someone here help me to modify the code to correctly create a "Previous" button?

Option Explicit

Sub Button1_Click()

    Const s_DestSheet As String = "MIRCALCULATION"
    Const s_DestRange As String = "F5"
    Const s_SrcSheet As String = "MIRDATA"
    Const s_SrcCell As String = "A1:A100"

    Static sidxCurrentCell As Variant: If IsEmpty(sidxCurrentCell) Then sidxCurrentCell = 1

    With Worksheets(s_SrcSheet).Range(s_SrcCell)
        sidxCurrentCell = (sidxCurrentCell + 1) Mod .Cells.Count
        .Cells(sidxCurrentCell + 1).Copy Destination:=Worksheets(s_DestSheet).Range(s_DestRange)
    End With    

End sub

Solution

That code was written (by me) for the specific case where there is only a Next button.

It would seem that changing the first + 1 to - 1 would be the way to modify the code to create a Previous button. This sort of works, but has a couple of issues which will be explained shortly. Before getting to that, however, note that the second + 1 must remain unchanged. It is there simply to convert the zero-base stored index to a one-base index as required by the Cell property. (And yes, for the Next button only case, it would have been simpler to store the one-base index instead. Fortuitously, storing the zero-based index turns out to be the simplest method for the case of both Next and Previous buttons.)

So, what are the issues with the - 1?

Well firstly, whilst it will correctly update the index to point to the previous row of the source range, it fails to wrap from the first row of the range to the last row. Instead, it results in a 1004 error (as .Cells(sidxCurrentCell + 1).Copy evaluates to .Cells(0).Copy). This is because the assignment statement containing the Mod operator was written in the simplest way to wrap from the last row to the first row, without allowing for the reverse case.

Secondly, since the current index is stored as a static variable in the subroutine called when a button is pressed, having two such subroutines means that there are two stored indexes operating independently of each other. Thus, assuming the indexes are both set to the second row, the sequence of button presses, Next+Next+Previous, will not result in displaying the third row's value, but will instead show the first row's value.

To solve the first issue, you need to add the number of rows of the source range to the first operand of the Mod operator. (Note that with this modification the Next subroutine will also continue to work correctly.)

The second issue is solved by using a generalised Previous/Next subroutine which takes a parameter to determine the direction, and assigning two other separate subroutines to each button respectively. These helper subroutines just call the main routine with the appropriate argument value (1 for the Next button and -1 for the Previous button). Thus, there is only one stored index which is used by both buttons.

The following is the full modified code where Button1 is the Next button, and Button2 is the Previous button:

'============================================================================================
' Module     : <any non-class module>
' Version    : 0.1.0
' Part       : 1 of 1
' References : N/A
' Source     : https://stackoverflow.com/a/53690885/1961728
'============================================================================================
Option Explicit

Private Sub Next_or_Previous( _
                                       ByRef direction As Long _
                            )
        Dim plngDirection As Long: plngDirection = direction

  Const s_DestSheet As String = "MIRCALCULATION"
  Const s_DestRange As String = "F5"
  Const s_SrcSheet As String = "MIRDATA"
  Const s_SrcCell As String = "A1:A100"

  Static sidxCurrentCell As Variant: If IsEmpty(sidxCurrentCell) Then sidxCurrentCell = -plngDirection

  With Worksheets(s_SrcSheet).Range(s_SrcCell)
    sidxCurrentCell = (sidxCurrentCell + plngDirection + .Cells.Count) Mod .Cells.Count
    .Cells(sidxCurrentCell + 1).Copy Destination:=Worksheets(s_DestSheet).Range(s_DestRange)
  End With

End Sub

Public Sub Button1_Click()
  Next_or_Previous 1
End Sub

Public Sub Button2_Click()
  Next_or_Previous -1
End Sub

Note that initialising sidxCurrentCell to -plngDirection results in the destination cell containing the value of the first row of the source range when either Next or Previous is first pressed after opening the workbook.