Switch between Excel Sheets like Windows [Alt]+[Tab] - Solution

[MR_Jason]

It has been a long time wish of mine to be able to switch between the new and last viewed Excel spreadsheet like switching between applications using [alt]+[tab] in windows. I have not been satisfied with the solutions I found. One recommendation was to open two versions of the same Excel file and select the two tabs you wanted to switch back and forth between using [alt]+[tab]. Not at all efficient. So, I decided to solve this on my own.

My solution involves an open workbook event to trigger a class module to define a new class object that keeps track of the last deactivated sheet. A procedure then activates the last deactivated sheet that can be triggered by a shortcut key.

To start, this code will have to be embedded in your Personal.xlsb file. If you do not know what this file is, read here copy-your-macros-to-a-personal-macro-workbook.

The following is written assuming you are familiar with the VB editor for Excel. Read here if you are not, managing-macros-with-the-visual-basic-editor.

In the project explorer (top left), double click the ThisWorkbook object under Microsoft Excel Objects tree and copy this code into it.

Private Sub Workbook_Open()
    Call Init_SheetToggle(a)
End Sub

Insert a new Module and copy this code into it. Name it anything you like. Default is Module1. I renamed mine to m_SheetToggle. You can change the name in the properties window. Display the properties window by pressing [F4] .

Dim AppObject As New c_SheetToggle
Public iSheet As String
Sub Init_SheetToggle(a)
    Set AppObject.AppEvent = Excel.Application
End Sub
Sub PriorSheet(sh)
    iSheet = sh.Name
End Sub
Sub SheetToggle()
    On Error Resume Next
    Sheets(iSheet).Select
End Sub

Insert a new Class Module. Press [F4] and change the name from Class1 to c_SheetToggle. This is not optional! Then, copy this code into it.

Public WithEvents AppEvent As Application
Private Sub AppEvent_SheetDeactivate(ByVal sh As Object)
    Call PriorSheet(sh)
End Sub

Save and close the VB editor.

Press [Alt]+[F8] and assign a shortcut key to the newly created routine under options. I have mine as [Ctrl]+[Shift]+Q. You may choose whatever you like.

Completely close all Excel files and restart Excel.