How can I know the previous row number that triggered a SelectionChange procedure?

Question

I have an Excel spreadsheet that calls a SelectionChange procedure every time I move the active cell.

This procedure carries out a certain number of actions and displays the results in a userform. The user can validate the results or cancel via command buttons.

Selection.row gives me the target cell's row.

How do I retrieve the row number of the cell I just left?

The first block of code below is in the sheet code, and the second is the userform code. This test userform contains just a label and a single button.

The first line of the button code works, but gives me the target row. I hoped that the second line would work, but OldRow is empty so the line crashes. How can I make OldRow visible to the userform?

Dim OldRow As Long

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Static OldRange As Range
    If Not OldRange Is Nothing Then
        ' do things
    End If
    MsgBox OldRow
    UserForm1.Show
    Set OldRange = Target.Cells(1, 1)
    OldRow = OldRange.Row
End Sub

Private Sub CommandButton1_Click()
    UserForm1.Label1.Caption = Selection.Row
    UserForm1.Label1.Caption = OldRow
End Sub

Answer 1

I would do it like that

Option Explicit

Dim PrevActiveCell As Range
Dim CurActiveCell As Range
Dim frm As New UserForm1

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Set PrevActiveCell = CurActiveCell
    Set CurActiveCell = ActiveCell

    If PrevActiveCell Is Nothing Then
        ' Either you forget to set CurActiveCell (when opening the workbook or ...)
        ' or you did reset the VBA project
    Else

        With frm
            .Caption = "Cur:" & CurActiveCell.Row & " Old: " & PrevActiveCell.Row
            .Show
        End With

    End If

End Sub

And in the form I have this code

Option Explicit

Private Sub CommandButton1_Click()
    Hide
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer _
                                       , CloseMode As Integer)
    ' Prevent the form being unloaded
    If CloseMode = vbFormControlMenu Then Cancel = True

    ' Hide the Userform
    Hide

End Sub

PS It is not a good idea to refer to the userform itself in the code of the userform. Reading material UserForm1.Show and Userform Default Instance vs. Userform Explicit Declaration