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!).
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.