Find cell address of previous active cell

[Google Excel]

Using VBA, how do you find the address of the previous active cell?
For example, suppose the active cell is A1. The user moves the cursor
to cell C3. After the cursor has moved, how can you find the address
of the previous active cell, i.e. A1?

 

[Mike Woodhouse]

Using VBA, how do you find the address of the previous active cell?
For example, suppose the active cell is A1. The user moves the cursor
to cell C3. After the cursor has moved, how can you find the address
of the previous active cell, i.e. A1?

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


Mike

 

[Google Excel]

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?

 

[Google Excel]

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