Unhide several worksheets at once

by Kara Hiltz

Application:

Microsoft Excel 2002/2003/2004/2007

Operating Systems:

Microsoft Windows, Macintosh

Excel lets you unhide only one worksheet at a time. But if you need to unhide five worksheets, you’ll find yourself repeatedly opening the Unhide window (Format | Sheet | Unhide) for each worksheet. Instead, we’ll show you how to gather your hidden worksheet names in a UserForm, select as many as you want, and then unhide them all simultaneously.

To build a VBA solution that lets us unhide several worksheets at once, we’ll:

• Create a dummy worksheet that collects all of your workbook’s hidden sheet names.

• Add a macro that deletes the dummy worksheet so you never even see it working behind the scenes.

• Design a UserForm that displays the hidden worksheet names and lets you select the ones you want to unhide.

Excel 2007 makes it slightly easier to unhide your worksheets by adding an Unhide Sheet option to the shortcut menu that displays when you right-click on a worksheet tab. But you can still only choose one worksheet to unhide at a time. We’ll give you a solution that lets you unhide several worksheets at once — without re-opening the same menu, as shown in Figure A. For hefty workbooks with a lot of hidden data, this macro will save you more time than you can imagine.

Note: We based this technique on the one we used in an article titled “Combat worksheet overload: Find the one you need on the fly,” which allowed you to find a worksheet based on wildcard text. To check out this article, go to www.elijournals.com/premier/showArticle.asp?aid=25140.

You can choose to unhide as many worksheets as you want simultaneously with our helpful code technique.

Scare up a “ghost” sheet to hold your sheet names

The first step is to gather your workbook’s hidden worksheet names. We’ll use a VBA procedure to create a “ghost” worksheet. Excel will cycle through each worksheet in your workbook. If the worksheet is hidden, Excel will add its name to the new hidden worksheet. You’ll end up with a hidden worksheet that lists the names of every hidden worksheet in the workbook. Later, we’ll have Excel delete the “ghost” worksheet so you never see it working behind the scenes.

To create a “ghost” sheet to store your hidden worksheet names:

1. Open the workbook that you want to work with. (Tip: If you want to make this macro available to all worksheets, store it in your personal.xls workbook.)

2. Press [Alt][F11] to open the Visual Basic Editor (VBE).

3. Choose Insert | Module from the VBE’s menu bar to open a blank code screen.

4. Enter the code in Listing A.

Download: You can copy and paste the code featured in this article from the file code.txt found at the URL given at the beginning of this article. You can also download a completed workbook at the same URL.

Listing A:

Code to create a “ghost” worksheet

Sub CreateWorksheet()

Dim intRow As Integer

Dim wrksht As Worksheet

Dim strName As String

Application.ScreenUpdating = False

strName = "Hidden Worksheets"

ActiveWorkbook.Worksheets.Add Before:=Worksheets(1)

ActiveWorkbook.Worksheets(1).Visible = False

ActiveWorkbook.Worksheets(1).Name = strName

intRow = 1

For Each wrksht In ActiveWorkbook.Worksheets

Select Case wrksht.Visible

Case xlSheetHidden

ActiveWorkbook.Worksheets(strName).Cells _

(intRow, 1) = wrksht.Name

intRow = intRow + 1

Case xlSheetVeryHidden

ActiveWorkbook.Worksheets(strName).Cells _

(intRow, 1) = wrksht.Name

intRow = intRow + 1

End Select

Next wrksht

Application.ScreenUpdating = True

End Sub

The CreateWorksheet() procedure adds a worksheet called Hidden Worksheets before the first worksheet in your workbook. Then, it sets its Visible property to