|
Home > Macros > Basics > Navigation Excel Sheet Lister Add-inQuickly select a sheet from this popup list that stays open while you work in Excel. Get the free workbook and instructions.
For other sheet listers, see Navigation Toolbar for Sheets 2003 and Navigation Command for Sheets |
Sheet Lister Intro
If you're working in an Excel file with lots of worksheets, it can take a while to scroll to the ones that you need. To make it easier, use this Sheet Lister add-in -- a popup list of Excel sheets. See the details below, and there's a link below where you can download the file
Thanks to Dave Peterson who contributed the original Navigation Toolbar for Sheets 2003.
Install the Sheet Lister Add-in
Use the Download link at the bottom of this page, to get the zipped file. After you unzip the file, follow the instructions on my Excel Add-ins page, to install the add-in.
Be sure to unblock the file, so that it will appear correctly in Excel. There are details in on the Excel Add-ins page, in the section titled, Add-in Custom Tab Not Showing
How It Works
The Sheet Selector add-in is a modeless UserForm, with a listbox for the sheet names.
The add-in command appears on the View tab. It's a split button, so you can click on the top part, or click the arrow, and choose a command from the drop down list.
While the Sheet Lister is open, you can continue to work in Excel. For example, copy cells on one sheet, then click the Sheet Lister to go to the destination sheet, and paste the data there.
And unlike some macros, the Sheet Lister doesn't clear out your Undo stack. That's because it just selects a sheet for you, it doesn't make any changes to the worksheets.
Use the Sheet List
When you click the Show List command (at the top of the button or in the drop down), the UserForm appears. The UserForm is modeless, so you can leave it open while you work in Excel.
Just click on a sheet name, to go to that sheet in the active workbook. If the sheet names are long, use the scroll bar at the bottom, to read the hidden part of the name.
If you'd rather see the sheet names in alphabetical order, click the A-Z button at the bottom.
If you switch to a different workbook, click either the Update button or the A-Z button, to see an updated list of sheets for the current workbook.
Problem With Windows
There is a problem with modeless UserForms, in the newer versions of Excel, which have multiple windows. The UserForms only appears in front of the workbook that was active when you opened the UserForm. It is hidden behind other workbooks that are also open.
To avoid that problem, I have two monitors, and keep the popup list at the left edge of the monitor that's to my right. I'm always able to see it that way, even if it's partially covered by one of the open workbooks.
If you have trouble keeping the Sheet Lister visible, Jan Karel Pieterse has code that fixes the problem. Get his code, and add it to the Sheet Lister workbook.
Download the Sample File
To use the Sheet Lister add-in, download the Sheet Lister sample file. The zipped file is in xlam format, and contains macros. There are installation instructions on my Excel Add-ins page, and remember to unblock the file.
The code is not locked, so you can go into the Visual Basic Explorer, to see how it works.
More Tutorials
Last updated: March 10, 2023 4:25 PM