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:
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:
- Press [Alt]+[F11] to launch the Visual Basic Editor (VBE).
- Double-click ThisWorkbook in the Project Explorer to open that module.
- 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.
- Click Save and close the VBE.
Now you’re ready to add the macro button that executes the code. To do so, do the following in Excel 2003:
- Choose Customize from the Tools menu.
- Click the Commands tab.
- From the Categories list, choose Macros.
- Drag Custom Button from the Commands list to a toolbar.
- Before closing the Customize dialog box, right-click the new macro button and choose Assign Macro.
- Select ThisWorkbook.ShowSheetLists and click OK.
- 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:
- Click the QAT dropdown and choose More Commands.
- From the Choose Commands From list, choose Macros.
- Select ThisWorkbook.ShowSheetLists and click Add.
- 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!