To hide a worksheet with VBA, set its Visible property to xlSheetHidden: ThisWorkbook.Worksheets("Sheet1").Visible = xlSheetHidden. To keep it out of Excel’s normal Unhide list, use xlSheetVeryHidden instead; save a copy before running a macro, because VBA actions cannot be reversed with Ctrl+Z.
What worksheet visibility means in Excel
Excel has three worksheet visibility states. A visible sheet appears as a tab and can be selected normally. A hidden sheet is removed from the tab row, but a user can usually bring it back through Excel’s Unhide command. A very hidden sheet is also removed from the tab row, but it does not appear in that standard Unhide dialog; VBA or the Visual Basic Editor is needed to change its visibility.
VBA represents these states with the Worksheet.Visible property:
xlSheetVisiblemakes a worksheet visible.xlSheetHiddenhides it in the usual way.xlSheetVeryHiddenhides it from the normal Unhide dialog.
These settings control whether a sheet is displayed, not whether its contents are protected. Hiding a sheet is useful for tidying a workbook or keeping supporting calculations out of the way, but it is not a reliable way to secure confidential information. A person with access to the workbook may still be able to inspect its contents or change its visibility.
How to run VBA that hides a worksheet
In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor. On a Mac, Option+F11 is the usual equivalent, although some keyboards require Fn as well. Excel for the web cannot run or edit VBA macros; open the workbook in the desktop application instead.
- In the editor, choose Insert > Module.
- Paste the macro below into the new module.
- Change
Instructionsto the exact worksheet tab name you want to hide. - Return to Excel and run
HideInstructionsfrom the Macro dialog or the Visual Basic Editor.
This example activates another visible worksheet before hiding the target. That avoids trying to leave Excel on a sheet that is no longer visible.
Sub HideInstructions()
Dim target As Worksheet
Dim candidate As Worksheet
' Identify the worksheet to hide in this workbook.
Set target = ThisWorkbook.Worksheets("Instructions")
' Do nothing if the target is already hidden.
If target.Visible <> xlSheetVisible Then Exit Sub
' Activate another visible worksheet before hiding the target.
For Each candidate In ThisWorkbook.Worksheets
If candidate.Name <> target.Name _
And candidate.Visible = xlSheetVisible Then
candidate.Activate
target.Visible = xlSheetHidden
Exit Sub
End If
Next candidate
' Excel cannot leave a workbook with no visible worksheet.
MsgBox "Another worksheet must be visible before this one can be hidden."
End Sub
The reference uses ThisWorkbook, meaning the workbook that contains the macro. If the code is stored in a separate macro workbook but should affect the workbook currently being used, ActiveWorkbook may be appropriate instead. Choose deliberately: the active workbook can change when a user opens or switches files.
Hide the active worksheet or a sheet by name
When you know the tab name, referring to it explicitly is generally clearer than relying on whichever worksheet happens to be selected. A name-based reference such as Worksheets("Instructions") documents the macro’s intent and reduces the risk of hiding the wrong sheet after a user changes the selection.
You can also hide the active worksheet by referring to ActiveSheet, but first make sure another sheet is visible and activate it. Hiding the selected sheet without handling the next active sheet can cause confusing behavior or an error in workbooks with unusual sheet arrangements. The example above takes the safer route by finding another visible worksheet.
Worksheet names must match the tab text exactly, including spaces and punctuation. If a name does not exist, VBA stops with a “Subscript out of range” error when it tries to set the reference. For a macro used by other people, add error handling or check for the worksheet’s existence before attempting to hide it.
When to use xlSheetVeryHidden
Use xlSheetVeryHidden when you want to prevent routine users from restoring a sheet through the normal Excel interface. For example, a workbook might use a configuration sheet for a dashboard, with the dashboard itself as the ordinary user-facing page. A macro can set the configuration sheet’s visibility to xlSheetVeryHidden while leaving the dashboard visible.
The assignment is the same as for a regular hide, with a different visibility constant:
Sub MakeInstructionsVeryHidden()
Dim target As Worksheet
Dim candidate As Worksheet
' Find the sheet to hide.
Set target = ThisWorkbook.Worksheets("Instructions")
' Move to another visible worksheet first.
For Each candidate In ThisWorkbook.Worksheets
If candidate.Name <> target.Name _
And candidate.Visible = xlSheetVisible Then
candidate.Activate
target.Visible = xlSheetVeryHidden
Exit Sub
End If
Next candidate
MsgBox "Another worksheet must be visible before this one can be hidden."
End Sub
VeryHidden is a convenience and interface-control feature, not encryption or access control. Someone who can use VBA may be able to change the property back, and workbook contents may be exposed in other ways. Do not place passwords, personal information, or other sensitive material in a sheet on the assumption that hiding it makes the data safe.
How to unhide a worksheet with VBA
To restore a hidden or very hidden worksheet, set its visibility to xlSheetVisible. The macro below works for either hidden state. It then activates the restored sheet so the result is easy to confirm.
Sub ShowInstructions()
Dim target As Worksheet
' Identify the worksheet to restore.
Set target = ThisWorkbook.Worksheets("Instructions")
' Make the sheet visible and select it.
target.Visible = xlSheetVisible
target.Activate
End Sub
If you only used ordinary hiding, you can also restore the sheet without VBA. In Excel for Microsoft 365 on Windows, right-click a worksheet tab, choose Unhide, select the sheet, and choose OK. Another route is Home > Cells > Format > Hide & Unhide > Unhide Sheet. The Unhide dialog lists ordinary hidden worksheets, but not very hidden ones.
Workbook structure protection and other limits
Workbook structure protection is different from protecting an individual worksheet. Sheet protection limits certain edits to cells; workbook structure protection restricts changes such as adding, deleting, moving, renaming, hiding, or unhiding sheets through the interface. In Windows Excel, workbook structure protection is managed from Review > Protect Workbook.
If structure protection is enabled, a VBA instruction that changes a worksheet’s visibility may fail. Unprotect the workbook structure before changing visibility, then restore protection if your workflow requires it. If a password is involved, use the workbook’s authorized password and process; VBA should not be treated as a way to bypass protection.
Excel also requires at least one worksheet to remain visible. A macro that attempts to hide the last visible worksheet will fail. This is why the examples look for another visible worksheet and stop with a message if they cannot find one. In a workbook that includes chart sheets or other non-worksheet sheet types, review the workbook’s layout before adapting the examples, because the simple search checks worksheet tabs only.
Common errors and how to diagnose them
Visibility macros are short, but a few predictable issues account for most failures. Check these points before changing the code:
- “Subscript out of range”: Confirm that the worksheet name in the code matches the tab name exactly and that
ThisWorkbookis the workbook containing that sheet. - Run-time error 1004: Check whether workbook structure protection is enabled and whether another worksheet is visible. Also confirm that the target is a worksheet in the workbook being referenced.
- The macro does not appear in the Macro dialog: Save the file in a macro-enabled format such as
.xlsm, check that the code is in a standard module, and confirm that macros are allowed by your organization’s security settings. - The sheet is missing but cannot be unhidden from the menu: It may be set to
xlSheetVeryHidden. Use the restore macro or change its property in the Visual Basic Editor. - The wrong workbook changes: Decide whether the procedure should refer to
ThisWorkbookorActiveWorkbook. The former is the workbook storing the macro; the latter is the workbook currently active in Excel.
When debugging, step through the macro in the Visual Basic Editor and inspect the workbook and worksheet references. Avoid changing a procedure to use ActiveSheet simply to make an error disappear; that can conceal a mistaken workbook reference and cause a different sheet to be changed.
VBA versus hiding a sheet manually
For a one-time change, using Excel’s built-in Hide command is often simpler than creating a macro. Right-click the sheet tab and choose Hide, or use the Home ribbon path described above. Use VBA when the visibility change is part of a repeatable workflow—for example, when a button opens a user-facing report and hides setup sheets, or when a workbook must restore a known sheet state each time a process runs.
Choose the visibility level based on the intended user experience:
- Choose
xlSheetHiddenwhen users should be able to unhide the sheet through Excel. - Choose
xlSheetVeryHiddenwhen the sheet should not appear in the ordinary Unhide dialog, while recognizing that this does not secure its contents. - Choose a different approach if the real requirement is to restrict access to data. Hiding is not a substitute for appropriate file permissions or secure storage.
Macros may be disabled by Excel or an organization’s security policy, and they do not run in Excel for the web. If the workbook must work without macros, keep the process manual or redesign the workbook so the required sheets can remain visible. Before distributing a macro-enabled workbook, make sure recipients understand what the code changes.
Frequently Asked Questions
Can VBA hide every worksheet in a workbook?
Excel requires at least one worksheet to remain visible, so a macro cannot hide the last visible worksheet. Make another sheet visible first, then hide the sheet you no longer want displayed.
What is the difference between xlSheetHidden and xlSheetVeryHidden?xlSheetHidden removes a sheet from view but leaves it available in Excel’s Unhide dialog. xlSheetVeryHidden removes it from that dialog too; VBA or the Visual Basic Editor is needed to restore it.
Can I unhide a VeryHidden sheet without VBA?
Not through the standard Unhide dialog. Change the sheet’s Visible property to xlSheetVisible in VBA or in the Visual Basic Editor. Workbook structure protection may need to be removed first.
Does hiding a worksheet protect its data?
No. Hiding controls visibility, not confidentiality. Ordinary hidden sheets can be unhidden from Excel, and VeryHidden sheets are not encrypted. Use suitable access controls rather than relying on a hidden tab to protect sensitive information.
Can I run Excel VBA in a browser or on a Mac?
Excel for the web does not run VBA macros; open the file in desktop Excel. Excel for Mac supports VBA, though keyboard shortcuts and some interface details differ from Windows. On many Mac keyboards, Option+F11 opens the Visual Basic Editor, sometimes with Fn.
Related
- Excel Unable to Merge Cells? Causes, Fixes, and Alternatives
- Excel Text to Columns Wizard: Split Data Easily
- Excel Text to Columns: Split Data Cleanly and Safely
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA COUNTA: Count Nonblank Cells in MacrosIn Excel VBA, count nonblank cells with Application.WorksheetFunction.CountA and pass it a range. For example, Application.WorksheetFunction.CountA(Worksheets("Data").Range("A2:A100")) returns the…
- VBA Double Quotes in Strings: How to Escape a QuoteTo include a double quote inside a VBA string, type two double quotes in a row: VBA treats…
- VBA MsgBox Arguments: Buttons, Icons, and ExamplesIn VBA, MsgBox displays a dialog and can return the button the user clicked. Its arguments are Prompt,…
- Excel VBA Runtime Error 70: Causes and FixesExcel VBA runtime error 70, “Permission denied,” means that a VBA operation was refused access to a file,…
- Excel VBA: Keep a UserForm on TopTo keep an Excel VBA UserForm above other windows on Windows, call the Windows API function SetWindowPos with…
- VBA PivotTable Object: How It Works and How to Use ItIn Excel VBA, a PivotTable object represents one PivotTable report in a worksheet: its fields, layout, values, and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.