Shortcut To Switch Back And Forth Between Two Excel Worksheet Tabs

Is There An ALT-TAB Excel Equivalent?

One of my all-time favorite keyboard shortcuts is ALT + TAB. This simple keyboard combination lets you toggle back and forth between the current program window and the last program window you were viewing. It is extremely handy when you are trying to verify information between two sources or simply trying to multitask. I also love this shortcut because every single PC user finds it useful, it is by no means a niche shortcut.

Since 95% of my world consists of interacting with Excel worksheets, I was extremely upset when I couldn’t find any trace of a similar shortcut to flip back and forth between two Excel tabs that I was analyzing. I searched high and low, but could only find two worksheet navigating keyboard shortcuts: 

1. CTRL + PAGE UP
2. CTRL + PAGE DOWN

The problem with these shortcuts is they only navigate through the order of your tabs so you would have to key them 7 times to get from tab #2 to tab #9 and at that point, you might as well grab a hold of your mouse.

Introducing ALT + ` (acute)

Since a shortcut to flip between Excel tabs didn’t seem to exist, I set out to devise a solution for my dilemma. What I was able to come up with is the most impactful piece of VBA code I’ve written for myself to date… and I am going to share it with you, NO STRINGS ATTACHED! 

With my proposed solution, you will be able to use the keyboard shortcut ALT+` (the acute sign is right above the TAB key on your keyboard) with Excel worksheet tabs as you would using ALT + TAB for program windows. I have named this amazingly useful piece of code: TabBack.

Adding The TabBack Macro Code

The following will show you step-by-step how to add the VBA code to your Personal Macro file so that you will always have this shortcut available to you while running Excel. 

Creating A Personal Macro File

First, if you have never triggered Excel to create a Personal Macro file you will need to do so. Here are the steps for creating it:

1. Go to the View Tab
2. Click the Macro button dropdown and select Record Macro…
3. In the Store Macro In drop down select Personal Macro Workbook
4. Click OK
5. Click a cell on your spreadsheet
6. Go back to the Macro button dropdown and select Stop Recording

That’s it! Excel has now created a Personal Macro file that will automatically open in the background every time you launch your Excel application. Now let’s paste in the code so you can add this awesome “tab back” functionality.

First you will need to launch your Visual Basic Editor while inside Excel. You can do this by using the keyboard shortcut ALT + F11.

Insert The ThisWorkbook Code

First, you will need to add an event-triggered macro at the workbook level of your Personal Macro workbook. This will ensure every time you open Excel, the TabBack shortcut will be available to you.

1. Expand the PERSONAL.XLSB project tree by clicking the plus sign next to the file name in the Project Explorer (use keyboard shortcut CTRL + R to view if it is now showing)
2. Inside the Microsoft Excel Objects folder, double-click on the ThisWorkbook object
3. Paste in the corresponding VBA code from this article into the ThisWorkbook object coding area

You can copy the code shown in the above screenshot here:

Private Sub Workbook_Open()
'PURPOSE: Run code when this workbook is opened
'SOURCE: www.TheSpreadsheetGuru.com

'Run the TabBack macro
  Call TabBack_Run

End Sub

Insert The Module Code

Next, you will need to add some VBA code into a module. This is where you will set the keyboard shortcut combination and execute the action of toggling between spreadsheet tabs.

1. In the Modules folder, double-click on the Module named Macro1. You will need to rename Macro1 to TabBack (use the keyboard shortcut F4 while Macro1 is selected to view the Properties window pane).
2. Double-Click the TabBack module name in the Project Window
3. Delete any miscellaneous code that was recorded when you initially created the Personal Macro workbook
4. Paste in the corresponding VBA code from this article into the TabBack Module coding area

You can copy the code shown in the above screenshot here:

Dim TabTracker As New TabBack_Class

Sub TabBack_Run()
'PURPOSE: Initiate Tab tracking and shortcut key trigger
'SOURCE: www.TheSpreadsheetGuru.com

'Enable TabTracker class
  Set TabTracker.AppEvent = Application
  
'Call ToggleBack macro when user keys alt + `
  Application.OnKey "%`", "ToggleBack"

End Sub

Sub ToggleBack()
'PURPOSE: Go Back to Previous Worksheet
'SOURCE: www.TheSpreadsheetGuru.com

With TabTracker
  On Error Resume Next
  Workbooks(.WorkbookReference).Worksheets(.SheetReference).Activate
  On Error GoTo 0
End With

End Sub

Insert The Class Module Code

In this final section, you will need to create a Class Module

1. Right-click on the Module folder
2. Go to Insert and select Class Module

Once you have a new Class Module create, there are a few more steps you will need to do.

1. In the Class Modules folder, double-click on the Class named Class1. You will need to rename Class1 to TabBack_Class (use the keyboard shortcut F4 while Class1 is selected to view the Properties window pane).
2. Double-Click the TabBack module name in the Project Window
3. Paste in the corresponding VBA code from this article into the TabBack_Class Class Module coding area
4. Save your Personal Macro file

You can copy the code shown in the above screenshot here:

Public WithEvents AppEvent As Application
Public SheetReference As String
Public WorkbookReference As String

Private Sub AppEvent_SheetDeactivate(ByVal Sh As Object)
'PURPOSE: Store active worksheet information before leaving it
'SOURCE: www.TheSpreadsheetGuru.com

  WorkbookReference = Sh.Parent.Name
  SheetReference = Sh.Name
  
End Sub

Private Sub AppEvent_WorkbookDeactivate(ByVal Wb As Workbook)
'PURPOSE: Store active worksheet information before closing workbook
'SOURCE: www.TheSpreadsheetGuru.com

  WorkbookReference = Wb.Name
  SheetReference = Wb.ActiveSheet.Name
  
End Sub

Running The TabBack Macro

Now that you have all your VBA code pasted in and saved, you can go ahead and exit the Visual Basic Editor. In order to start using the TabBack keyboard shortcut, you simply need to restart Excel.

Now all you need to do is practice your ALT + ` finger positions. I find that using your thumb for the ALT key and your middle finger for the Acute key is the most comfortable.

VBA Coding Not Your Thing?

I can understand if all this VBA coding stuff seems scary or too time-consuming to fiddle around with. That is why I went ahead and coded an Excel add-in file so you can simply install it and start using tab toggling functionality. Feel free to directly download the spreadsheet by clicking the download button below.

Room For Improvement?

I am in no way very experienced with creating Classes in VBA. If you see something in my code that is missing or would improve the functionality, please let me know in the comments section so I can make any adjustments. I have been using this code for a while now and haven’t run into any issues to-date, but as with all coding I’m sure there are ways to improve it. Let me know your thoughts!

Hidden Hacks For VBA Macro Coding

After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!

Learn These Hacks Now For Free!

Keep Learning

Explore more →

Lock An Image’s Aspect Ratio

What This VBA Code Does This code allows you to lock and unlock the Aspect Ratio of a shape or...

VBA Pattern Validation (Characters And Numbers)

Today we are going to discuss how you can use VBA to ensure that cell values match a specific pattern. ...

Center An Object (Textbox, Shape, etc…) On Your Slide

What This VBA Code Does Basically, anything you can select on your PowerPoint slide is considered an object. What this...

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.