Previously active cell

Question

For the code I am writing I monitor the changes in certain cell ranges to run functions and private subs. For this I use the Intersect function in the worksheet_change sub.

However, the trigger for the intersect 'test' is always that I 'move out' of the cell I am testing for whether it'd be via mouseclick into a different cell or via cursor move.

What I need is a way to define a variable which contains the .address of the cell I had selected before.

I tried the code below, but all I get is errors.

Does anybody have an idea how to do this?

Public xfrLastCell As String
Public xfrActiveCell As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If xfrActiveCell = "" Then xfrActiveCell = ActiveCell.Address
xfrLastCell = xfrActiveCell
xfrActiveCell = ActiveCell
MsgBox xfrLastCell

End Sub

Solution 1

below code works for me - your assignment of activecell was missing an Address meaning activecell variable is always blank

Option Explicit

Public xfrLastCell As String
Public xfrActiveCell As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If xfrActiveCell = "" Then xfrActiveCell = ActiveCell.Address
xfrLastCell = xfrActiveCell
xfrActiveCell = ActiveCell.Address
MsgBox xfrLastCell

End Sub

Solution 2

With this code, referring PreviousActiveCell will return desired result:

Public PreviousActiveCell as Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Static pPrevious as Range
    Set PreviousActiveCell = pPrevious
    Set pPrevious = ActiveCell
End Sub

This works inside single worksheet. Do you need a previous cell across other sheets and workbooks?

Solution 3

It might be easier to "remember" the Range rather than the address of the range:

Dim Oldcell As Range
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Oldcell Is Nothing Then
        Set Oldcell = Target
        Exit Sub
    End If
    MsgBox "New cell is " & Target.Address & vbCrLf & "Old cell was " & Oldcell.Address
    Set Oldcell = Target
End Sub