This method works on the second and subsequent cursor movement but
obviously not on the first cursor movement since prevTarget and
currTarget are initially null. Any ideas on how to capture the
previous active cell on the first cursor movement?

Mike Woodhouse wrote:
I can only think of tracking SelectionChange events. Try putting this
in your worksheet's Module:

Option Explicit
Dim prevTarget As Range
Dim currTarget As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next ' Lazy workaround for empty objects first time through
Set prevTarget = currTarget
Set currTarget = Target
Debug.Print "Now at " & currTarget.Address & ", previously at " & prevTarget.Address
On Error GoTo 0
End Sub

The following Auto_Open macro will capture the cursor position when the
spreadsheet is opened:

Public cellAddress As String

Sub Auto_Open()
cellAddress = ActiveCell.Address
End Sub

Your code can then be modified to use cellAddress as the initial range
for prevTarget as shown below:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo Target_Error
Set prevTarget = currTarget
Set currTarget = Target
MsgBox "Now at " & currTarget.Address & ", previously at " &
prevTarget.Address
On Error GoTo 0
Exit Sub

Target_Error:
Set prevTarget = Range(cellAddress)
Set currTarget = Target
MsgBox "Now at " & currTarget.Address & ", previously at " &
prevTarget.Address
End Sub


Google Excel wrote:
This method works on the second and subsequent cursor movement but obviously not on
the first cursor movement since prevTarget and currTarget are initially null. Any ideas on
how to capture the previous active cell on the first cursor movement?

Mike Woodhouse wrote:
I can only think of tracking SelectionChange events. Try putting this
in your worksheet's Module:

Option Explicit
Dim prevTarget As Range
Dim currTarget As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next ' Lazy workaround for empty objects first time through
Set prevTarget = currTarget
Set currTarget = Target
Debug.Print "Now at " & currTarget.Address & ", previously at " & prevTarget.Address
On Error GoTo 0
End Sub