VBA Hide Sheet in Excel — Hidden, Very Hidden, and Why Neither Is a Lock

TL;DR — ws.Visible = xlSheetHidden hides a sheet; xlSheetVisible shows it again. There is a third state, xlSheetVeryHidden, which removes the sheet from the Unhide dialog so only code or the VBA editor can bring it back. All three change what the user sees, not what the sheet is: a hidden sheet still calculates, still feeds every formula that points at it, and is fully readable and writable from code. Excel refuses to hide the last visible sheet (error 1004), and you cannot Select a hidden one — you never need to. Hide sheets to tidy a workbook; if the content must stay secret, it does not belong in the file.

Sub HideHelperSheets()
    With ThisWorkbook
        .Worksheets("Lookups").Visible = xlSheetHidden        ' user can unhide via right-click
        .Worksheets("Config").Visible = xlSheetVeryHidden     ' not listed in the Unhide dialog
    End With
End Sub

This is the second of a three-part cluster on a sheet's identity. The tab name is a label the user owns. Rename changes that label, hide, covered here, takes the tab off screen without taking the sheet out of reach, and check if a sheet exists asks whether a label is taken. The thread through all three: the tab strip is a view of the Sheets collection. Hiding edits the view; the collection does not change.

What you'll learn

The mental model: hiding edits the view, not the workbook
The three Visible states and what each one allows
The two errors: the last visible sheet, and selecting a hidden sheet
Working with hidden sheets without ever showing them
Unhiding every sheet in one loop, and hiding all but one
Why neither hidden nor very hidden is security, and what to do instead

The mental model: hiding edits the view

A workbook's sheets live in the Sheets collection. The row of tabs at the bottom is a view of that collection, and hiding removes a tab from the view — nothing else. The sheet keeps its place in the collection, its index, its name, its formulas and its data. So:

A formula like =Lookups!B2 keeps working when Lookups is hidden.
Worksheets.Count still counts it, and Worksheets(2) may well be a hidden sheet.
A For Each loop over Worksheets visits it.
Code can read and write its cells exactly as before.

That is why hiding is the right tool for helper sheets — lookup tables, configuration, staging data — and the wrong tool for anything you need to protect. It is the sheet-level version of hiding columns: zero display, full presence.

The three Visible states

Visible takes one of three XlSheetVisibility constants:

Constant	Value	Tab shown	In the Unhide dialog	Who can show it again
`xlSheetVisible`	-1	yes	—	—
`xlSheetHidden`	0	no	yes	anyone, with right-click > Unhide
`xlSheetVeryHidden`	2	no	no	code, or the VBA editor's Properties window

ws.Visible = False also works and means xlSheetHidden, but write the constants: True and False cannot express the third state, and readers should not have to remember that False is 0.

Very hidden is the useful one for tools you build. The user never sees the sheet listed, so they do not unhide your configuration out of curiosity and edit it by accident. To set it without code, select the sheet in the VBA editor's Project window and change Visible in the Properties window.

The two errors you will hit

Hiding the last visible sheet. A workbook must always show at least one sheet. Hiding the only visible one raises run-time error 1004, Unable to set the Visible property of the Worksheet class. Very hidden and hidden sheets do not count, so a workbook with one visible sheet and ten hidden ones still refuses. When you hide sheets in a loop, make sure the sheet that should stay visible is visible before the loop starts.

Selecting a hidden sheet. Worksheets("Lookups").Select on a hidden sheet raises error 1004. The same applies to selecting a range on it. The fix is not to unhide it first — it is to not select at all:

' Wrong: select a hidden sheet to write to it
' Worksheets("Lookups").Select
' Range("A1").Value = "Updated"

' Right: write through a reference; the sheet stays hidden
ThisWorkbook.Worksheets("Lookups").Range("A1").Value = "Updated"

The Select and Activate guide explains why this is the better habit on visible sheets too. There is a third, rarer error: if the workbook's structure is protected, changing any sheet's Visible property fails until you call Unprotect on the workbook.

Unhide every sheet, or hide all but one

The Unhide dialog can only show sheets that are xlSheetHidden. To bring back everything, very hidden sheets included, loop the Sheets collection — Sheets, not Worksheets, so chart sheets come back too:

Sub UnhideAllSheets()
    Dim sh As Object
    For Each sh In ThisWorkbook.Sheets
        sh.Visible = xlSheetVisible
    Next sh
End Sub

The reverse — show one dashboard and hide everything else — has to respect the last-visible rule, so the order matters. Show the keeper first, then hide the rest:

Sub ShowOnly(keep As Worksheet)
    Dim sh As Object
    keep.Visible = xlSheetVisible          ' first, so there is always one visible sheet
    For Each sh In keep.Parent.Sheets
        If Not sh Is keep Then sh.Visible = xlSheetVeryHidden
    Next sh
    keep.Activate
End Sub

keep.Parent is the workbook the sheet belongs to, so the routine works on any open workbook, not just the one holding the code.

The judgment call: hiding is presentation, not security

It is tempting to treat very hidden as a lock. It is not. Anyone can open the VBA editor with Alt+F11, see the very hidden sheet in the Project window, and set it back to visible. Locking the VBA project with a password closes that door for casual users, and protecting the workbook structure stops right-click Unhide — but the data is still in the file. Any tool that reads .xlsx files directly, from Power Query to a Python script, reads every sheet regardless of its Visible property, and hidden sheets travel with every copy of the workbook you send.

My rule: hide to reduce clutter and prevent accidents; remove to keep secrets. Use very hidden for configuration the user should not stumble into, add structure protection if you want the Unhide menu greyed out, and never ship salary tables, cost prices or credentials in a hidden sheet. If a recipient must not see it, delete it from the copy you send — the Delete Sheet guide shows how to do that without the confirmation prompt.

How ExcelMaster helps

Visibility bugs are quiet: a macro that stops at error 1004 only when the user has already hidden the other sheets, a report that selects a hidden sheet and fails on one machine, a "secret" pricing sheet that went out to a customer inside the workbook.

ExcelMaster lets you describe what you want — "show only the Dashboard sheet, make the setup sheets very hidden, and protect the structure" — and writes the visibility code in the order that never trips the last-visible rule, working with hidden sheets through references instead of selecting them.

Frequently asked questions

How do I hide a sheet with VBA?

Set its Visible property: Worksheets("Data").Visible = xlSheetHidden. The user can unhide it with right-click > Unhide. Use xlSheetVeryHidden if the sheet should not appear in the Unhide dialog at all, and xlSheetVisible to show it again.

What is the difference between hidden and very hidden?

A hidden sheet appears in the Unhide dialog, so any user can bring it back. A very hidden sheet does not appear there; only code or the Properties window in the VBA editor can make it visible again. Both still calculate and both are fully accessible from code.

How do I unhide all sheets in VBA?

Loop the Sheets collection with For Each and set sh.Visible = xlSheetVisible on every sheet. This also restores very hidden sheets and chart sheets, which the Unhide dialog cannot bring back.

Why do I get error 1004 when hiding a sheet?

Most often because it is the last visible sheet, and a workbook must always show at least one. Make another sheet visible first. The other cause is a protected workbook structure, which blocks any change to sheet visibility until the workbook is unprotected.

Can VBA read and write a hidden sheet?

Yes. Hiding only removes the tab from view, so Worksheets("Data").Range("A1").Value works the same on a hidden sheet. What fails is Select or Activate, which you do not need: write through a reference and the sheet stays hidden.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-09-30.