Excel VBA Code to Hide and Unhide Sheets

VBA & macros · Updated November 22, 2026

The quickest VBA code to hide a sheet is Sheets("Sheet1").Visible = xlSheetHidden, and to unhide it use Sheets("Sheet1").Visible = xlSheetVisible. For a more complete solution, the macro below lets you hide or unhide all sheets except the active one, or toggle a specific sheet by name.

Why Use VBA Instead of Right-Clicking?

Excel’s built-in hide feature (right-click a sheet tab > Hide) works fine for one or two sheets, but it becomes tedious when you need to hide dozens of sheets, hide sheets based on a condition, or hide sheets that you don’t want users to easily unhide. VBA gives you control over the Visible property of each sheet, which can be set to three states:

  • xlSheetVisible (-1) – the sheet is shown normally.
  • xlSheetHidden (0) – the sheet is hidden but the user can unhide it via the right-click menu.
  • xlSheetVeryHidden (2) – the sheet is hidden and does not appear in the Unhide dialog box. Only VBA can unhide it.

This distinction is important for security or for preventing accidental changes to complex workbooks.

Basic Hide and Unhide Macros

The simplest one-liner hides a single sheet by name. To hide a sheet named “Data”, use:

Sheets("Data").Visible = xlSheetHidden

To unhide it, use:

Sheets("Data").Visible = xlSheetVisible

If you want to hide the sheet that is currently active (the one the user is looking at), use:

ActiveSheet.Visible = xlSheetHidden

To unhide all sheets in the workbook, loop through all sheets:

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    ws.Visible = xlSheetVisible
Next ws

These snippets assume you are working in the VBA editor (press Alt+F11 on Windows, Option+F11 on Mac). Always save a copy of your workbook before running any macro, because hiding or unhiding sheets cannot be undone with Ctrl+Z.

Hiding All Sheets Except One

A common task is to hide every sheet except the one the user is currently viewing, which creates a clean interface. The following macro does that:

Sub HideAllExceptActive()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> ActiveSheet.Name Then
            ws.Visible = xlSheetHidden
        End If
    Next ws
End Sub

To unhide everything afterward, run the “unhide all” loop from the previous section. You can assign these macros to buttons on the ribbon or to shapes on a sheet for easy access.

Using VeryHidden to Prevent Unhiding

If you want to hide a sheet so that users cannot unhide it from the Excel interface, use xlSheetVeryHidden. For example:

Sheets("Config").Visible = xlSheetVeryHidden

To unhide it, you must run VBA (or use the Immediate Window in the VBA editor):

Sheets("Config").Visible = xlSheetVisible

Note that this is not a security feature – a determined user can still access the VBA project if the workbook is unprotected. To prevent viewing the code, protect the VBA project with a password (Tools > VBAProject Properties > Protection in the VBA editor). But even then, it is not foolproof.

Common Problems and Their Fixes

Several issues arise when hiding sheets with VBA:

  • “Subscript out of range” error – This happens when the sheet name in the code does not exist. Double-check the spelling and ensure the sheet is not a chart sheet (use Worksheets instead of Sheets if you only want worksheet tabs).
  • Cannot hide the last visible sheet – Excel requires at least one visible sheet. If you try to hide all sheets, you get an error. The code above avoids this by keeping the active sheet visible.
  • Unhide dialog is grayed out – If all sheets are set to xlSheetVeryHidden, the Unhide option is disabled. You must run VBA to unhide them.
  • Macro runs but nothing happens – Check if the sheet is already in the desired state, or if the code is in a different workbook than the one you are looking at. Use ThisWorkbook to refer to the workbook containing the macro.

Comparing Hide Methods: Manual, VBA, and Sheet Properties

Excel offers three ways to hide sheets, each with different strengths:

  • Manual hide (right-click tab) – Quick, no code, but only hides one sheet at a time and is easily undone by users.
  • VBA with xlSheetHidden – Automates hiding multiple sheets, but users can still unhide via the right-click menu.
  • VBA with xlSheetVeryHidden – Removes the sheet from the Unhide list, giving better control, but requires VBA to reverse.
  • Sheet properties (in the Properties window) – In the VBA editor, you can set the Visible property of a sheet directly in the Properties window (F4). This is a manual way to apply VeryHidden without writing code.

For most users, VBA with xlSheetHidden is sufficient. Use xlSheetVeryHidden when you need to prevent accidental unhiding, such as for configuration sheets or lookup tables.

Frequently Asked Questions

How do I hide all sheets except the first one?

Modify the loop to check for a specific name. For example, to hide all sheets except “Main”, replace ActiveSheet.Name with "Main" in the code above. Or, use a more direct loop:

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Main" Then ws.Visible = xlSheetHidden
Next ws

Can I hide sheets based on a cell value?

Yes. For instance, if cell A1 on “Control” contains “Hide”, you can hide a sheet named “Data” with:

If ThisWorkbook.Sheets("Control").Range("A1").Value = "Hide" Then
    ThisWorkbook.Sheets("Data").Visible = xlSheetHidden
End If

Place this in a Worksheet_Change event or a button macro.

Why can’t I unhide a sheet from the right-click menu?

Because the sheet is set to xlSheetVeryHidden. Only VBA can change it back to visible. Run the unhide-all macro from the VBA editor, or use the Immediate Window (Ctrl+G) and type Sheets("SheetName").Visible = xlSheetVisible.

Does hiding a sheet affect formulas that reference it?

No. Hidden sheets remain fully functional; formulas continue to calculate normally. The only difference is that the sheet is not visible to the user. This is a common way to keep lookup tables or helper columns out of sight.

Can I hide sheets in Excel for the web or Mac?

Excel for the web does not support VBA macros at all. On Mac, VBA is available in Excel 365, but the keyboard shortcuts differ (use Option+F11 to open the VBA editor). The code itself works the same on both platforms. For Excel for the web, you must use the manual hide method (right-click tab > Hide).

Safety and Best Practices

Before running any macro, save a copy of your workbook. Macros cannot be undone with Ctrl+Z, so if you accidentally hide the wrong sheet, you may need to re-run a macro to unhide it. Also, if you share the workbook, remember that hidden sheets are not protected – users can unhide them unless you use xlSheetVeryHidden and protect the VBA project. For sensitive data, consider using sheet protection or workbook-level protection instead of relying solely on hiding.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.