PDA

View Full Version : [SOLVED] Macro to move back to previously active cell



aymericdv
09-02-2016, 08:20 AM
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

offthelip
09-02-2016, 03:32 PM
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:
Private Sub Worksheet_Activate()


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


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

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


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

mikerickson
09-02-2016, 05:45 PM
You could put this in the ThisWorkbook code module

' 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


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

aymericdv
09-07-2016, 06:51 AM
Hi Mike

That works brilliantly!

Cheers

AsphyXiated
06-14-2018, 04:46 AM
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


You could put this in the ThisWorkbook code module

' 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


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

mikerickson
06-14-2018, 06:02 AM
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.

AsphyXiated
06-14-2018, 07:17 AM
Hi Mike,

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

Thanks again.

albertan
05-03-2019, 06:35 AM
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?

grosner
07-21-2019, 01:18 PM
You could put this in the ThisWorkbook code module

' 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


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

grosner
07-21-2019, 01:22 PM
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!).