Contextures

Excel Sheet Lister Add-in

Quickly 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.

sheet lister popup

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.

sheet lister command

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.

sheet list and scroll bar

If you'd rather see the sheet names in alphabetical order, click the A-Z button at the bottom.

sheet list A-Z button

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.

sheet list Update button

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

Worksheet Tips

Worksheet Protection Macro

Worksheet VBA

Hide Sheets Based on Tab Colour

Worksheet AutoFilter VBA

Copy VBA to Sheet

VBA - Get Started

Search Contextures Sites

 

Excel Tools Add-in

Free Pivot Table Tools

 

 

 

 

Excel Data Entry Popup List

 

 

 

 

Last updated: March 7, 2017 8:42 AM
Contextures RSS Feed