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.
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] .
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.
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.
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks