Software

Add a macro that displays the Excel Worksheet Navigation list

Offer users a bit of sheet-jumping flexibility. Use an Excel macro to display the Sheet Navigation list, anytime, anywhere.

Moving around in an Excel sheet quickly becomes second nature. There are a number of ways: scrolling, [F5], the Name box, arrow keys, and so on. Moving from sheet to sheet is just as easy, but perhaps not as intuitive. Of course, you can click a sheet tab, but if you have a lot of sheets, they're not all visible. You can use the keyboard shortcuts [Ctrl]+[Page Up] and [Ctrl]+[Page Down] to scroll through the sheets, but with lots of sheets, that can take awhile. Fortunately, there's a nice shortcut. Right-click the Sheet Navigation toolbar. (To the left of the sheet tabs). Excel will display a list of all the sheets in the workbook, with the current sheet checked. Simply select a sheet in the list to select that sheet.

The Sheet Navigation toolbar is available by default, but the sheet's creator can turn off this display as follows:

2003

2007

2010

  1. Choose Options from the Tools         menu.
  2. Click the View tab.
  3. Uncheck the Sheets Tab option in the Windows Options section.
  4. Click OK.

  1. Click the Office button and click Excel Options.
  2. Click Advanced in the left pane.
  3. In the Display Options For This Worksheet section, uncheck Show Sheet Tabs.
  4. Click OK.

  1. Click the File tab and choose Options (under Help).
  2. Click Advanced in the left pane.
  3. In the Display Options For This Worksheet section, uncheck Show Sheet Tabs.
  4. Click OK.

TechRepublic's Microsoft Office Suite newsletter, delivered every Wednesday, is designed to help your users get the most from Word, Excel, and Access.
Turning off the Sheet Navigation Toolbar is a consequence of disabling the sheet tabs. If you want to turn off the sheet tabs but still allow users the ease of jumping to sheets via the Sheet Navigation list, you can add a macro that displays the same list. First, you need to add the sub procedure (macro) that displays the list, as follows:

  1. Press [Alt]+[F11] to launch the Visual Basic Editor (VBE).
  2. Double-click ThisWorkbook in the Project Explorer to open that module.
  3. Enter the sub procedure shown below. This simple statement will display the same list of sheets. Simply select an item in the list to select that sheet.
  4. Click Save and close the VBE.

Sub ShowSheetLists()
  Application.CommandBars("Workbook tabs").ShowPopup
End Sub
Now you're ready to add the macro button that executes the code. To do so, do the following in Excel 2003:

  1. Choose Customize from the Tools menu.
  2. Click the Commands tab.
  3. From the Categories list, choose Macros.
  4. Drag Custom Button from the Commands list to a toolbar.
  5. Before closing the Customize dialog box, right-click the new macro button and choose Assign Macro.
  6. Select ThisWorkbook.ShowSheetLists and click OK.
  7. Now click Close to close the Customize dialog box. Click the new macro button to see the sheet list.

If you're using Excel 2007 or Excel 2010, add the macro to the Quick Access Toolbar (QAT), as follows:

  1. Click the QAT dropdown and choose More Commands.
  2. From the Choose Commands From list, choose Macros.
  3. Select ThisWorkbook.ShowSheetLists and click Add.
  4. Click OK to return to the workbook. The button is ready to go. Simply click it to display a list of sheets. Select a sheet from the list to select that sheet.

As long as the Sheet Navigation toolbar is available, this technique hardly seems worth the effort. On the other hand, it's so easy that you may think of a few automated tasks that might benefit from displaying the sheet list.

Tell us how you put this macro to use!

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

8 comments
berapard
berapard

Very useful tip, already in my own "toolbox" ! Is it possible to code something like that to jump from one workbook to an other one ?

lsangree
lsangree

"Right-click the Sheet Navigation toolbar. (To the left of the sheet tabs). Excel will display a list of all the sheets in the workbook" When I do this in Excel 2007, it only shows the first 15 tabs and on the 16th line it says "More Sheets...". How do you make it show "all of the sheets," because that would be truly useful! The pop-up shows about the same number of tabs that are already visible on the bottom of the screen.

dajomu1
dajomu1

You can jump between sheets using ctrl ? page up/down

jbenton
jbenton

This has gone straight into my personal.xls and I've added a keyboard shortcut to the macro button so now I can jump sheets without using the mouse

dhays
dhays

Two methods come to mind. The old method of Alt-Shift moves you between programs open, not just Excel Workbooks, or you could set up a macro to follow the steps of selecting Window and the other workbook in question, it might be that Excel would set the other Workbook name in the macro, making it not as convenient, unless you work with one set all of the time.

dhays
dhays

You not only jump, but since you are using the shift key to get the ?, you end up selecting all sheets jumped to, so make sure you realize what you are doing and don't destroy any data or delete all the sheets selected.

jbenton
jbenton

but only to next/previous, here you can see the whole list to select from and use home/end to go to the first or last using this in a left hand keyboard/right hand mouse combination reduces scrolling and makes the hugely-laborious task of intersheet navigation so much easier

jbenton
jbenton

alt-tab surely? to jump between open workbooks in the order last visited, use ctrl-tab or ctrl-sh-tab to scroll backwards

Editor's Picks