Macro to move back to previously active cell

Hi there

Is anyone able to write a macro that allows the user to return to the cell that was last active. I want to be able to use a shortcut that returns me to the cell through which I have followed a link (by using CTRL + [). I have given it a go but am struggling.

Many thanks,

Aymeric

I did something similar for a client who wanted a "Back" button which would take the user to the previously selected sheet. This could be modified to go to the previous sheet and also the cell:
I put this code into every sheet in the workbook:
[VBA]Private Sub Worksheet_Activate()


ActiveWorkbook.Names.Add Name:="prevsheet", RefersTo:=ActiveWorkbook.Names("currsheet")
ActiveWorkbook.Names.Add Name:="currsheet", RefersTo:=ActiveSheet.Name
End sub
[/VBA]

and I ran this code when the "Back" button was selected:

[VBA]Sub Goback()Dim str As String
Dim Lent As Integer


str = (ActiveWorkbook.Names("prevsheet"))
Lent = Len(str)
str = Mid(str, 3, Lent - 3)
Worksheets(str).Activate


End Sub
[/VBA]

You will need to add some similar code to the selection change event for each sheet to save the activecell on every sheet, and then go there on the "Back" button

You could put this in the ThisWorkbook code module

Code:

---

' in ThisWorkbook code module

Public PrevActiveCell As Range
Dim CurActiveCell As Range

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Set PrevActiveCell = CurActiveCell
    Set CurActiveCell = ActiveCell
End Sub

---

and then call this (in a normal module) to return to the previous cell

Code:

---

Sub GoBack()
    If Not ThisWorkbook.PrevActiveCell Is Nothing Then
        Application.Goto ThisWorkbook.PrevActiveCell
    End If
End Sub

---

Hi Mike

That works brilliantly!

Cheers

Hi Mike

I have tried to enter the below via VB for a button in Excel 2016.
I'm a novice with VB, Could you please tell me where precisely I should put this code.

First section of code in This Wokbook --> Insert --> Module ?
Second Part of code in where?

The way I've been trying seems promising but highlights the Sub GoBack() section and PrevActiveCell.

With some message returning in excel.

I hope you can help.

Asph

Quote:

---

Originally Posted by mikerickson

You could put this in the ThisWorkbook code module

Code:

---

' in ThisWorkbook code module

Public PrevActiveCell As Range
Dim CurActiveCell As Range

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Set PrevActiveCell = CurActiveCell
    Set CurActiveCell = ActiveCell
End Sub

---

and then call this (in a normal module) to return to the previous cell

Code:

---

Sub GoBack()
    If Not ThisWorkbook.PrevActiveCell Is Nothing Then
        Application.Goto ThisWorkbook.PrevActiveCell
    End If
End Sub

---

---

The window created by Insert>Module is where the GoBack sub goes.

If you look at the Project Explorer window, you'll see some Excel Objects, one of which is the ThisWorkbook module. That module is where the PrevActiveCell routine goes.

Hi Mike,

Worked like a dream. I think was over-complicating it.

Thanks again.

Quote:

---

Originally Posted by mikerickson

The window created by Insert>Module is where the GoBack sub goes.

If you look at the Project Explorer window, you'll see some Excel Objects, one of which is the ThisWorkbook module. That module is where the PrevActiveCell routine goes.

---

Hello

Thank you so much for this code. Is it possible to record something like this in my Personal Macro Workbook?

Quote:

---

Originally Posted by mikerickson

You could put this in the ThisWorkbook code module

Code:

---

' in ThisWorkbook code module

Public PrevActiveCell As Range
Dim CurActiveCell As Range

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Set PrevActiveCell = CurActiveCell
    Set CurActiveCell = ActiveCell
End Sub

---

and then call this (in a normal module) to return to the previous cell

Code:

---

Sub GoBack()
    If Not ThisWorkbook.PrevActiveCell Is Nothing Then
        Application.Goto ThisWorkbook.PrevActiveCell
    End If
End Sub

---

---

Is there a way to enhance GoBack with a cell history stack? In other words, the ability to GoBack (GoForward) in the list of cells a user has selected (and in the same order!).