Ability To Navigate Back To Previous Cell Selected

[miketurn]

I have seen many posts about this but have not really found anything that works for me.
I am looking for a hotkey or macro that allows you to go back to the previous cell selected.
I am currently using 2003, and I have tried F5 / CTRL+G, someone else mentioned somewhere to try CTRL + Backspace which did not work either.

I would even take a macro if anyone has one already created?

Example:
Say you cut or copy text from cell A100 and you scroll up to A1 and paste it, I am looking for a quick simple one button action that navigates back to A100

It is not extremely important but could be a nice tool to have, may also be a nice piece of code to have to add this feature to other macros.

If anyone has anything like this, please let me know.
Thank You

 

[RickXL]

Hi,

This looks promising:

Private SelectionStack   As Object

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Const StackSize     As Long = 50
    
    If SelectionStack Is Nothing Then Set SelectionStack = New Collection
    SelectionStack.Add Target.Address(, , True, True)
    If SelectionStack.Count > StackSize Then SelectionStack.Remove (1)
End Sub

Sub goBack()
    Application.EnableEvents = False
    On Error GoTo err
    With SelectionStack
        If .Count <= 1 Then
            MsgBox "No items in stack"
        Else
            Application.Goto (.Item(.Count - 1))
            .Remove (.Count)
        End If
    End With
err:
     Application.EnableEvents = True
End Sub

I put that into the ThisWorkbook module. With a slight modification it could be made to work in a Personal workbook as well. That way, it would work across workbooks.

It creates a Collection called SelectionStack.
When the selection is changed for the first time the Collection is created.
Every time a selection is changed, the full address (in R1C1 format - trick to make Application.Goto work) is added to the Collection.

I also created a Quick Access Toolbar button that called the ThisWorkbook.goBack macro.
When you click that button, the items in the Collection will be read from the end and Excel will go to that selection.

If the stack becomes empty then a message is displayed. It currently holds 50 items (set by Const StackSize As Long = 50). Change as necessary.
If the Collection has not been created when the goBack macro is run, it fails on the .Count property and skips to the end.


Regards.

 

[miketurn]

@RickXL
Thank you for your response, I am sorry it took me so long to get back here but Windows 7 has taken me on quite a journey.
Thank you for supplying this macro.

 

[RickXL]

@RickXL
... Windows 7 has taken me on quite a journey.
Thank you for supplying this macro.

No problem.

If all software just worked and was entirely intuitive then lots of people would be without a job.


Regards,