How can I obtain the address of the cell I am leaving? 2

[NigelHarper]

If I am in cell A10 in a sheet and move to an adjacent cell either by arrow keys, return key, tab key or by selecting another non-adjacent cell with my mouse; how do I pass the address of the cell I am leaving on to a variable. Many events seem to only look at the target cell but is there one I can use to look at the cell I am leaving. Thanks.

 

[ide]

insert a sheet into your book naming "PrevRange"
hide this worksheet

insert a sub into all the worksheet code one by one (Sheet 1 code, sheet 2 code..):

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
On Error Resume Next
MsgBox ActiveWorkbook.Worksheets("PrevRange&quot.Range("A1&quot.Offset(ActiveSheet.Index, 0)
ActiveWorkbook.Worksheets("PrevRange&quot.Range("A1&quot.Offset(ActiveSheet.Index, 0) = Target.Address
End Sub

this sub sends in an msgbox the address of the previous cell on the active sheet, and store the adddress of current cell on the sheet above, in the row that equal to the activesheet index of the workbook collection object

i hope it is what you need.
ide

 

[RobBroekhuis]

Ide's method works great, and is a good general solution. If you have a limited number of sheets and you want to avoid creating the additional worksheet, you can store the previous address in each worksheet's code:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Static ThisCell As Range
   If Not (ThisCell Is Nothing) Then MsgBox (ThisCell.Address)
   Set ThisCell = Target
End Sub

Rob