Consulting

Results 1 to 7 of 7

Thread: Macro to move back to previously active cell

  1. #1

    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

  2. #2
    VBAX Expert
    Joined
    May 2016
    Posts
    593
    Location
    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

  3. #3
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,770
    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

  4. #4
    Hi Mike

    That works brilliantly!

    Cheers

  5. #5
    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 View Post
    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

  6. #6
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,770
    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.

  7. #7
    Hi Mike,

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

    Thanks again.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •