Categories
Excel

Unhide Hidden or Very Hidden Worksheets

Sometimes you want a worksheet that users can’t read or modify, you can use the Excel hide and unhide feature. You can also use the xlSheetVeryHidden constant in VBE to hide the worksheet tab so deep that it cannot be turned visible again unless you use the VBA or VBE.

  1. Hide/Unhide Worksheets
  2. Make Very Hidden Worksheets
  3. Make Workbook Window Hidden

Hide or Unhide Worksheets

To hide worksheets in Excel, you can use the following steps:

  1. Right-click on the worksheet tab that you want to hide or unhide. You can select multiple worksheets by holding Ctrl and clicking on the tabs.
  2. Select Hide from the context menu.
  3. The worksheet will disappear from the workbook.

To unhide worksheets use the following steps:

  1. Right-click on any visible worksheet tab
  2. Select Unhide from the context menu.
  3. The Unhide dialog box will appear with a list of hidden worksheets.
  4. Select the worksheet that you want to unhide.
  5. Click OK.

Alternatively, you can use the ribbon to hide and unhide worksheets:

  1. Click Home tab
  2. Locate the Cells group
  3. Click Format > Hide & Unhide > Hide Sheet or Unhide Sheet option.

xlVeryHidden – Make Very Hidden Worksheets

Each Excel worksheet has three options for its Visible property. These options are represented by constants in the xlSheetVisibility enumeration and are as follows:

  1. xlSheetVisible : -1 (default for new sheets)
    The worksheet is visible in the sheet tab bar and can be freely selected and viewed.
  2. xlSheetHidden : 0
    The worksheet is hidden from the sheet tab bar and is not selectable. You can unhide such sheets by right-clicking on the sheet tab (see Hide or Unhide Worksheets)
  3. xlSheetVeryHidden : 2
    The worksheet is only accessible through the Visual Basic Editor. The sheets can be accessed by named reference within VBA code, allowing easy use of the stored data

Let’s fiddle with the worksheet’s Visible property, making it xlVeryHidden:

  1. Press Alt+F11 to open VBE (Visual Basic Editor).
    (Select View » Project Explorer if the Project Explorer window is not visible.)
  2. Find the name of your workbook within the Project Explorer and expand its hierarchy by clicking the + to the left of the workbook’s name.
  3. Expand the Microsoft Excel Objects folder within to reveal all your workbook’s worksheets.
  4. Select the sheet you want to make very hidden from the Project Explorer.
    (Reveal worksheet properties by selecting View » Properties Window if not visible).
  5. Make sure the Alphabetic tab is selected, and look for the Visible property at the very bottom.
  6. Click the value box on the right associated with the Visible property and select the last option, 2 – xlSheetVeryHidden, as shown in the figure.
  7. Press Alt-Q to save your changes and return to Excel.

The sheet will no longer be visible via the Excel interface and no longer appears when you use Excel’s Unhide feature

To reverse the process, simply follow the preceding steps, this time selecting -1 – xlSheetVisible.

Hide or Unhide Workbook Window

You cannot hide all the worksheets in a workbook. There must be at least one visible worksheet at all times. If you want to hide a workbook window entirely, you can use the View tab > Window group > Hide or use Unhide option to unhide the window.

Understanding Workbooks and Worksheets: