Popup List of Excel Sheets

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. Sometimes you can’t even remember where the sheets are, and that takes even longer! To make it easier for myself, I created an add-in with a popup list of Excel sheets. See the details below, and there’s a link to my site where you can download it.

A Bit of History

Long ago, the talented Dave Peterson created a menu bar combo box that listed all the sheets in the active workbook. Select a sheet from the drop down list, and instantly go to that sheet.

That add-in was designed for Excel 2003, but it still works in the newer versions – it appears on the Add-ins tab. You can download it on the Sheet Selector for Excel 2003 page.

sheetlister04

Sheet Selector 2007

When the Office interfaced changed in 2007, Ron de Bruin created a new version of Dave’s Sheet Selector. It adds a command on the Home tab of the Ribbon. Get Ron’s version on the Excel Navigator List page of my website.

Click the Select a sheet command, and a popup list appears, with all the sheet names. Then, click one to go to that sheet.

sheetlister05

A Longer List

If there are 16 or more sheets in the workbook, the Activate window opens instead. That’s the list that appears when you right-click on the sheet scrolling buttons at the bottom left of the Excel window.

In the Activate window, click a sheet name, then click the OK button, to go to that sheet.

So that takes 3 click (Select a Sheet, sheet name, OK), and who has time for that?

sheetlister06

Sheet Lister 2017

To save some time and reduce clicks, I decided to create a new version of the Sheet Selector. My version 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.

sheetlister01

The Sheet Lister Popup

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.

If the sheet names are long, use the scroll bar at the bottom, to read the hidden part of the name.

sheetlister02

Sheet Names A-Z

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

And if you switch to a different workbook, click the Update button or the A-Z button, to see an updated list of sheets.

sheetlister03

Keep Working in Excel

While the Sheet Lister is open, you can keep working in Excel. For example, copy something on one sheet, then click the Sheet Lister to go to the destination sheet, and paste it there.

And unlike some macros that clear out your Undo stack, the Sheet Lister doesn’t do that. That’s because it doesn’t make any changes to the worksheets – it just selects a sheet for you.

Problem with Multiple Windows

The modeless UserForms don’t play well with the newer versions of Excel, with the multiple windows. They only  appear in front of the workbook that was active when you opened the UserForm.

Jan Karel Pieterse has screen shots that show the problem with modeless UserForms, and has some code that fixes the problem. So, if you’re using my Sheet Lister, and are having trouble with the Excel windows, you can add his code to the Sheet Lister workbook.

For now, I’m leaving it as is. 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.

Get the Sheet Lister Add-in

To get a copy of my Sheet Lister add-in, go to the Excel Sheet Lister Add-in page on my Contextures website. The zipped file is in xlam format, and contains macros.

NOTE: When you’re installing an add-in, follow the instructions here, to unblock the file. Otherwise, it might not appear on the Excel Ribbon, due to a Microsoft security update.

The code is not locked, so you can see how it works, and even adjust the size of the Userform and listbox, if you’d like to.

popup list of excel sheets

__________________

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.