Shortcut to toggle back to the last worksheet you were working in. Is there such a thing?

[lgrande]

Hi,
I was wondering if you can toggle back to the last worksheet you were working in. I have many worksheets and it takes too much time to find the last worksheet I was in. Any help would be appreciated, I do not want to have to create a VBA or Macro. I'm still learning how to do them. Thanks so much.

Linda G.

 

[6StringJazzer]

There are built-in keyboard shortcuts to navigate to the next and previous sheets by tab positions, but there are no buttons or keystrokes for the last active sheet. That would require VBA.

 

[Logit]

Excel Keyboard Shortcuts List - Excel Campus

 

[lgrande]

Excel Keyboard Shortcuts List - Excel Campus

Thanks so much Jeff, I appreciate you taking the time to respond.

 

[6StringJazzer]

Thanks so much Jeff, I appreciate you taking the time to respond.

The message you quoted was from Logit. He provided a link to a comprehensive set of all keyboard shortcuts. But you'll see the one you wanted isn't there. Here are the ones that do apply to sheet navigation. "Next" and "Previous" here refer to tab order.

Shortcut	Windows	Mac	Web
Insert new worksheet	Shift+F11	Fn+Shift+F11	Shift+F11
Delete selected worksheets	Alt,E,L		Alt,H,D,S
Rename current worksheet	Alt,O,H,R
Go to next worksheet	Ctrl+PgDn	Fn+Ctrl+↓	Ctrl+Alt+PgDn
Go to previous worksheet	Ctrl+PgUp	Fn+Ctrl+↑	Ctrl+Alt+PgUp
Select adjacent worksheets	Ctrl+Shift+PgUp/PgDn
Select adjacent worksheets (mouse)	Shift+Click	Shift+Click
Select non-adjacent worksheets (mouse)	Ctrl+Click	Cmd+Click
Open Move or Copy window for selected sheets	Alt,E,M
Duplicate worksheet (mouse)	Ctrl+drag	Alt+drag
Protect or Unprotect Sheet	Alt,R,P,S
Clear print area	Alt,P,R,C
Set print area	Alt,P,R,S

 

[Logit]

This is a DIY macro method that you can assign to a 'hot key' .

Paste the following in ThisWorkbook module :

Option Explicit


Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
LastSheet = Sh.Name
End Sub

Paste the following into a regular module :

Option Explicit

Public LastSheet As String

Sub Select_Last()
Application.Sheets(LastSheet).Select
End Sub

To set a 'hot key' that activates the macro .... go to the DEVELOPER TAB and click on Macros.

Highlight the macro name "Select_Last", then click on OPTIONS. Now select a keyboard key to go along with the key CTRL.
Perhaps Ctrl+n ?
Click OK

Now each time you press CTRL + n, you will be taken back to the last sheet that was active.
NOTE: you will need to save the workbook ... close the workbook ... then reopen the workbook before the macro will be available.

 

[6StringJazzer]

Note: You have to do this in every workbook where you want this to work. Also note OP requested a solution without VBA.

 

[6StringJazzer]

Here is a VBA solution that is installed into the PERSONAL workbook and will allow going "back" to any sheet in any workbook.

Excel does not provide a built-in way to return to the previously active worksheet. This VBA provides a method for doing this.

To use this solution, install the following code into the PERSONAL.xlsb workbook. There are three pieces of code that must be copied into three different places. Then add a button that calls the sub, as described in the code comments for the first module.

Create a new standard module with any name, and copy this code into it:

Option Explicit

' Provide the ability to create a Back button to return to the
' previously active sheet

' This code uses a class with state that tracks sheet changes for all
' open workbooks. If the VBA code is stopped (such as if an unhandled error
' occurs and the user hits End, or a developer clicks the Stop button
' in the VBA development window) then the tracker object is destroyed
' and will have to be started again. Note that this is done automatically
' on a failed attempt to go to the last sheet, but past information is lost.

Dim SheetTracker As New ClassSheetChangeTracker
  
' This instantiates the class used to track sheet navigation so
' must be called when this workbook is opened
Public Sub StartSheetTracker()
   Set SheetTracker.appevent = Application
End Sub

' Add a button to the Ribbon or the Quick Access Toolbar that references
' this sub to return to the previously active worksheet
Public Sub GoToLastSheet()
  
   On Error GoTo CannotDoIt
  
   ' An error will occur if the SheetTracker is not instantiated, or
   ' it cannot find the previous active sheet for the workbook
   SheetTracker.LastSheet(WorkbookName:=ActiveWorkbook.Name).Activate

   Exit Sub
  
CannotDoIt:

   If SheetTracker Is Nothing Then
  
      MsgBox "Sheet tracker not found, restarting"
      StartSheetTracker
  
   Else
      MsgBox "Unable to determine previous sheet for " & ActiveWorkbook.Name
  
   End If
  
End Sub

Create a new class called ClassSheetChangeTracker and paste the following code into it:

Option Explicit

Public WithEvents appevent As Application

Dim PreviousSheets As scripting.Dictionary


Private Sub Class_Initialize()

  Set PreviousSheets = CreateObject("Scripting.Dictionary")

End Sub

' This is an event callback function that will be called
' any time that a sheet in any open workbook is deactivated
Private Sub appevent_SheetDeactivate(ByVal Sh As Object)

   If PreviousSheets.Exists(key:=Sh.Parent.Name) Then
      PreviousSheets.Remove key:=Sh.Parent.Name
   End If
  
   PreviousSheets.Add key:=Sh.Parent.Name, Item:=Sh
  
   'MsgBox "Left sheet " & Sh.Name
  
End Sub

Property Get LastSheet(WorkbookName As String) As Worksheet

   Set LastSheet = PreviousSheets(key:=WorkbookName)

End Property

Add the following sub into the ThisWorksheet module. If you already have a sub called Workbook_Open in that module, add the line of code shown.

Option Explicit

Private Sub Workbook_Open()
   SheetTracking.StartSheetTracker
End Sub

 

[MARK858]

ThisWorksheet module

Hi Jeff, I think you meant ThisWorkbook

 

[6StringJazzer]

Hi Jeff, I think you meant ThisWorkbook

You are, of course, correct. There is no ThisWorksheet module.