VeryHidden Sheet Revealer: Find and Reveal the Sheets Excel Won't Show You

TL;DR: You inherit a macro-enabled workpaper and something’s off. Formulas reference sheets you can’t see. The Unhide dialog shows nothing. The prior developer hid lookup tables, rate schedules, and configuration sheets using xlSheetVeryHidden — a VBA-only visibility level that the Excel UI can’t touch. This macro finds every one of those invisible sheets and makes them visible in one click.

#Prerequisites & Setup

What you’ll need:

• Excel 2016+ (desktop)
• A macro-enabled workbook (.xlsm, .xlsb) or any workbook where someone may have used VBA to hide sheets
• The workbook must be open — this macro works on ThisWorkbook

What the macro does:

• Scans every sheet in ThisWorkbook for xlSheetVeryHidden visibility
• Lists every VeryHidden sheet by name in a confirmation MsgBox
• On confirm, sets each to xlSheetVisible so they appear in the tab bar
• Reports the count of revealed sheets

Limitations:

• Does not reveal sheets hidden at the regular level (xlSheetHidden) — those already appear in the Unhide dialog. Right-click any tab → Unhide handles them natively
• Works on ThisWorkbook only. Store in your Personal Macro Workbook (PERSONAL.XLSB) and replace ThisWorkbook with ActiveWorkbook to run on any open file
• Does not unprotect or modify worksheet protection. If a VeryHidden sheet is protected, it will become visible but remain protected — you’ll need to unprotect it separately to edit
• If the workbook structure is protected, the operation will fail. The error handler reports this and you’ll need to unprotect the workbook first (Review → Protect Workbook)

#The Macro

Option Explicit

Sub RevealVeryHiddenSheets()
    ' ── Reveal Very Hidden Sheets ──────────────────────
    ' Finds every sheet set to xlSheetVeryHidden
    ' (the VBA-only visibility level), lists them
    ' in a confirmation dialog, and makes them all
    ' visible with one click.
    '
    ' Regular hidden sheets are NOT affected — those
    ' already appear in Excel's native Unhide dialog.
    ' ────────────────────────────────────────────────────

    ' ── State management ───────────────────────────────
    Application.ScreenUpdating = False

    ' ── Variables ──────────────────────────────────────
    Dim ws As Worksheet
    Dim count As Long
    Dim sheetList As String

    ' ── Error handling ─────────────────────────────────
    On Error GoTo CleanUp

    count = 0
    sheetList = ""

    ' ── Scan for VeryHidden sheets ─────────────────────
    For Each ws In ThisWorkbook.Worksheets
        If ws.Visible = xlSheetVeryHidden Then
            count = count + 1
            sheetList = sheetList & "  • " & ws.Name & vbCrLf
        End If
    Next ws

    ' ── No VeryHidden sheets ───────────────────────────
    If count = 0 Then
        MsgBox "No VeryHidden sheets found. All hidden " & _
               "sheets appear in the Unhide dialog.", _
               vbInformation, "Reveal Very Hidden Sheets"
        GoTo CleanUp
    End If

    ' ── Ask for confirmation ───────────────────────────
    If MsgBox("Found " & count & " VeryHidden sheet(s):" & _
              vbCrLf & vbCrLf & sheetList & vbCrLf & _
              "Make them visible?", _
              vbYesNo + vbQuestion, _
              "Reveal Very Hidden Sheets") = vbNo Then GoTo CleanUp

    ' ── Reveal them ────────────────────────────────────
    For Each ws In ThisWorkbook.Worksheets
        If ws.Visible = xlSheetVeryHidden Then
            ws.Visible = xlSheetVisible
        End If
    Next ws

    MsgBox "Revealed " & count & " VeryHidden sheet(s). " & _
           "They now appear in the sheet tab bar.", _
           vbInformation, "Reveal Very Hidden Sheets"

CleanUp:
    Application.ScreenUpdating = True

    If Err.Number <> 0 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description & _
               vbCrLf & vbCrLf & _
               "The workbook structure may be protected. " & _
               "Unprotect it first (Review → Protect Workbook) " & _
               "and try again.", _
               vbCritical, "Macro Error"
    End If
End Sub

#How It Works

#Three visibility levels, only two in the UI

Excel sheets have three possible values for the Visible property:

Value	Constant	What It Means
-1	xlSheetVisible	Sheet appears normally in the tab bar
0	xlSheetHidden	Sheet is hidden. Right-click any tab → Unhide shows it
2	xlSheetVeryHidden	Sheet is hidden AND excluded from the Unhide dialog. Only VBA can change this

The key insight: xlSheetHidden and xlSheetVeryHidden both hide the sheet. The difference is that xlSheetVeryHidden also hides it from the unhide mechanism. The sheet is visible only in the VBA Project Explorer — a place most tax preparers never look.

This macro targets only xlSheetVeryHidden sheets because xlSheetHidden sheets can be revealed natively. There’s no reason to duplicate functionality Excel already provides.

#The confirmation is the whole point

Some macros on this blog skip confirmation because the action is non-destructive — showing hidden rows or clearing filters doesn’t change data. Revealing VeryHidden sheets is different.

The developer who set those sheets to VeryHidden had a reason. Maybe it was “keep staff from editing the rate tables.” Maybe it was “hide the audit log so the client doesn’t see it.” Maybe it was “prevent the partner from questioning the assumptions.” You don’t know — and that’s why the MsgBox lists every sheet by name before you commit.

Found 3 VeryHidden sheet(s):

  • RateTables
  • Config
  • AuditLog

Make them visible?

If you see a sheet called “DoNotTouch_DevNotes” in the list, you know to investigate before revealing. If you see “RateTables,” you know it’s probably supposed to be hidden from casual view but you need to see it anyway. The list turns an opaque operation into an informed decision.

#Why the Calculation toggle isn’t needed

This macro doesn’t write to cells, doesn’t modify formulas, and doesn’t change cell values. It only changes a sheet-level property: Visible. No formulas recalculate, no dependencies update, no volatile functions fire. The only performance optimization needed is Application.ScreenUpdating = False, which prevents Excel from redrawing the tab bar after each sheet is revealed.

#The error handler covers three common failures

The CleanUp handler reports a tailored message for the most likely failure: workbook structure protection. If someone protected the workbook structure (Review → Protect Workbook), VBA can’t change sheet visibility at all — the ws.Visible = xlSheetVisible assignment raises a runtime error before any sheets are modified.

The error message tells the user exactly what to do:

MsgBox "The workbook structure may be protected. " & _
       "Unprotect it first (Review → Protect Workbook) " & _
       "and try again.", vbCritical, "Macro Error"

Two other failure modes are handled implicitly:

• All sheets already visible: the count = 0 check exits before the confirmation dialog, so the user never sees an empty “Found 0” prompt
• Sheet name with special characters: VBA references sheets by object, not name, so unusual characters in sheet names have no effect

#Adapt It