How can I obtain the address of a cell I am leaving.

[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.

 

[pcdaveh]

Can you explain to me what your trying to do or accomplish?

 

[NigelHarper]

I am trying to tell excel to go back to where it cam from and then get the row and column values and go to another worksheet and only calculate a range based on those row and column values of the original cell.

 

[pcdaveh]

Have you ever programatically walked the around a spread sheet. Like Range(MyColumn & MyStartRow).Select or

Cells(j, i).select

 

[pcdaveh]

Paste this code into a module and then hit your F8 Key.
Make sure that you can see your worksheet while your pressing the f8 key. do this repeatedly.

Sub TakeAWalk()
Dim i As Integer
Dim j As Integer
Dim a As String
Dim b As Integer


j = 1
a = "A"
b = 1

For i = 1 To 10
Cells(i, j).Select
Next i


' next method
Do Until b = 10
Range(a & b).Select
b = b + 1
Loop


End Sub

 

[logius]

Put this code in your Worksheet section through the VBA editor. If you're not sure how to do it, this faq68-1271 will give you an idea. Make sure you put it in the Worksheets events section, though.

Public LastCell As String


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not (LastCell = "") Then
        Target.Value = "The last cell was: " & LastCell
    End If
    LastCell = Target.Address(False, False)
    
End Sub

Hopefully this code will give you an idea of how to work with the last cell address.
----------------------------------------
If you are reading this, then you have read too far...

lightwarrior@hotmail.com