To unhide a worksheet with VBA, set its Visible property to xlSheetVisible, such as Worksheets("Summary").Visible = xlSheetVisible. For a normal hidden sheet, you can also right-click any sheet tab, choose Unhide, select the sheet, and click OK.
Unhide a sheet from Excel without VBA
If you only need to reveal one worksheet, Excel’s built-in command is usually the quickest option. In Excel for Microsoft 365 on Windows, right-click a visible sheet tab and choose Unhide. Select the worksheet in the list and confirm. You can also use Home > Cells > Format > Hide & Unhide > Unhide Sheet.
On a Mac, Control-click a visible sheet tab to open its context menu, then choose Unhide. The ribbon layout can vary slightly by Excel build, but the sheet-tab context menu is a useful route. If you use Excel for the web, use the sheet-tab menu to unhide a regular hidden worksheet; VBA macros themselves must be run in the desktop Excel application.
The Unhide dialog only lists sheets that Excel considers normally hidden. If a sheet is not listed, it may be set to VeryHidden, the workbook structure may be protected, or the sheet may belong to a different open workbook. Those cases need a different check.
What sheet visibility means in VBA
Excel exposes worksheet visibility through the Visible property. The property has three states: xlSheetVisible means the sheet is shown, xlSheetHidden means it can be restored through Excel’s Unhide command, and xlSheetVeryHidden means that command does not offer it. VeryHidden is a visibility setting, not a password or encryption feature.
VBA code can change a sheet’s visibility by setting the property to xlSheetVisible. Use the worksheet name exactly as it appears on the tab, including spaces. A workbook can also contain chart sheets, which are not worksheets; use the Worksheets collection when you specifically want to work with worksheet tabs. The broader Sheets collection can include both worksheets and chart sheets.
Unhide one named worksheet with a macro
Use this example when you repeatedly need to reveal a known worksheet. In the Visual Basic Editor, choose Insert > Module, paste the code into the module, and run the macro while the target workbook is the workbook containing the code. In this example, ThisWorkbook refers to that workbook.
Save a copy of the workbook before running a macro. VBA actions cannot be reversed with Ctrl+Z, so a backup is the reliable way to recover the previous state.
Sub UnhideSummarySheet()
' ThisWorkbook is the workbook that contains this macro.
Dim targetBook As Workbook
Dim targetSheet As Worksheet
Set targetBook = ThisWorkbook
Set targetSheet = targetBook.Worksheets("Summary")
targetSheet.Visible = xlSheetVisible
End Sub
Change Summary to the actual tab name. If the sheet name is missing or misspelled, VBA will report an error when it tries to find that worksheet. If the code is stored in a different workbook, such as a personal macro workbook, ThisWorkbook points to the file containing the macro, not necessarily the workbook you are looking at. In that situation, set targetBook to the intended workbook, for example with Set targetBook = ActiveWorkbook, after confirming the correct workbook is active.
Unhide every worksheet in a workbook
When a workbook has many hidden worksheets, a loop can make each worksheet visible in one run. This example affects worksheets in the workbook that contains the macro; it does not change chart sheets.
As with any macro that changes a workbook, save a backup copy first. The change is not undone by Ctrl+Z.
Sub UnhideAllWorksheets()
' Make every worksheet in this workbook visible.
Dim targetBook As Workbook
Dim targetSheet As Worksheet
Set targetBook = ThisWorkbook
For Each targetSheet In targetBook.Worksheets
targetSheet.Visible = xlSheetVisible
Next targetSheet
End Sub
This macro deliberately reveals all worksheets, including sheets that were hidden for navigation or presentation reasons. If you want only one sheet revealed, use the named-sheet macro instead. For a workbook that contains sensitive information, do not assume a hidden tab protects its contents; users with access to the file may be able to reveal hidden data.
How to reveal a VeryHidden sheet
A VeryHidden worksheet is omitted from Excel’s normal Unhide dialog. To change it through the Visual Basic Editor, press Alt+F11 on Windows to open the editor. In the Project Explorer, select the correct workbook and worksheet, then open the Properties window if it is not already visible. Find the Visible property and change it from 2 - xlSheetVeryHidden to -1 - xlSheetVisible. You can also use the VBA statement shown above.
On a Mac, the Visual Basic Editor can be opened from Developer > Visual Basic; the keyboard shortcut may depend on the keyboard and function-key settings. If Project Explorer or Properties is hidden, use the editor’s View menu to display the relevant window. Take care to select the correct workbook and sheet before editing the property.
VeryHidden is sometimes used to keep setup, lookup, or calculation tabs out of sight in a workbook interface. It does not stop someone who can edit the workbook from using VBA or the editor to inspect the sheet. It should not be used as a security boundary for confidential data.
Workbook structure protection and other limitations
Workbook structure protection is different from protecting an individual worksheet. Structure protection can prevent sheets from being added, deleted, moved, renamed, hidden, or unhidden. If it is active, trying to change Visible through VBA may produce an error, and the Unhide command may be unavailable. You need permission and, where required, the password to remove that protection.
If you are authorized to unprotect the workbook and know its password, VBA can remove structure protection before changing the sheet. The code below is only a pattern: replace the example password with the real password, and do not place an actual password in a macro that will be shared with people who should not know it.
Sub UnprotectAndUnhideSummary()
' Use only when you are authorized and know the password.
Dim targetBook As Workbook
Dim targetSheet As Worksheet
Set targetBook = ThisWorkbook
targetBook.Unprotect Password:="replace-with-password"
Set targetSheet = targetBook.Worksheets("Summary")
targetSheet.Visible = xlSheetVisible
End Sub
Unprotecting a worksheet with Worksheet.Unprotect is not the same operation as unprotecting workbook structure. A protected sheet can still be visible; sheet protection mainly limits editing of cells and other sheet content. If the workbook is protected by an organization or its password is unknown, ask its owner or administrator rather than trying to bypass the restriction.
Common errors and how to diagnose them
If the macro does not work, identify the exact workbook, sheet, and protection state before changing code. These checks resolve many common problems:
- The sheet name cannot be found: Confirm that the code points to the workbook containing the tab and that the name is spelled correctly. A workbook-qualified reference is safer than relying on whichever workbook happens to be active.
- Excel says the workbook is protected: Check whether workbook structure protection is enabled. You need authorization and the correct password to remove it.
- The sheet is absent from the Unhide list: It may be VeryHidden rather than normally hidden. Inspect its
Visibleproperty in the Visual Basic Editor or set it with VBA. - The macro appears to run in the wrong file:
ThisWorkbookmeans the workbook containing the code, whileActiveWorkbookmeans the workbook currently active in Excel. Choose deliberately. - Excel blocks or does not offer the macro: The file may be saved in a macro-free format, macros may be disabled, or the code may not be in a runnable standard module. Check the file format and your organization’s macro policy.
Excel must keep at least one worksheet visible. A macro that tries to hide the final visible worksheet will fail; this is a different limitation from unhiding a sheet. Also check that you are not confusing hidden sheet tabs with a hidden sheet-tab bar. If every tab is missing, the display setting or workbook window may be the issue rather than the visibility of a particular worksheet.
Running VBA safely on Windows, Mac, and the web
On Windows desktop Excel, press Alt+F11 to open the Visual Basic Editor, or choose Developer > Visual Basic. If the Developer tab is not displayed, enable it through File > Options > Customize Ribbon. In the editor, use Insert > Module for a standard module, then run a selected macro with F5 or return to Excel and use Developer > Macros.
Excel for Mac supports VBA, although some Windows-specific VBA features and automation integrations differ. Open the editor from the Developer tab and use the menu commands if the keyboard shortcut is inconvenient; on some keyboards you may need the Fn key with the function-key shortcut. Excel for the web does not run VBA macros. Open the workbook in the desktop app to use these procedures. Keep the workbook in a macro-enabled format such as .xlsm if you need to retain VBA code.
Only enable macros in files you trust. A macro can make changes beyond sheet visibility, and a hidden worksheet is not proof that a file is safe. Avoid lowering macro security settings globally just to run one workbook; follow your organization’s policies and verify the source of the file.
When to use the menu, VBA, or the editor
Choose the method based on what you need to change. The built-in Unhide command is convenient for a one-time change to a normally hidden worksheet. A short VBA macro is useful when the same task recurs, when a VeryHidden sheet must be shown, or when you need to reveal several worksheets consistently. The editor’s Properties window is helpful for inspecting a sheet’s current visibility state without writing a macro.
For workbooks shared with others, consider whether the sheet should remain visible after the task. Unhiding a tab changes the workbook’s presentation for everyone who opens the saved file. If you only need to inspect data, make a copy first and avoid saving changes to the shared original unless that is intended.
Frequently Asked Questions
Can VBA unhide a VeryHidden sheet?
Yes. Set that worksheet’s Visible property to xlSheetVisible. VeryHidden sheets do not appear in Excel’s ordinary Unhide dialog, but VBA or the Visual Basic Editor can change their visibility.
Why is the Unhide option grayed out?
There may be no normally hidden worksheet in the active workbook, or workbook structure protection may be restricting sheet changes. A VeryHidden sheet also will not be offered in the normal Unhide list.
Does protecting a worksheet prevent it from being unhidden?
Not by itself. Worksheet protection limits editing of sheet content. Workbook structure protection is the setting that can restrict hiding and unhiding sheets.
Can I unhide a sheet in Excel for the web with VBA?
No. Excel for the web does not run VBA macros. Use its sheet-tab menu for a normally hidden sheet, or open the workbook in desktop Excel to run VBA or change a VeryHidden sheet.
Can I undo an unhide macro with Ctrl+Z?
No. Excel’s undo history does not reverse VBA actions. Save a separate copy before running a macro, especially if it reveals multiple sheets or changes a shared workbook.
Related
- Conditional Formatting for Duplicates in Excel
- How to Merge Cells in Excel: Steps, Options, and Fixes
- Split Cell Contents Into Multiple Columns in Excel
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel UserForm Option Button: Setup and VBA GuideAn Excel UserForm option button (also called a radio button) lets a user choose one item from a…
- Excel VBA Delete a Workbook: Safe Methods and CodeYes. Excel VBA can delete a workbook file from disk, but the workbook must be closed first: Workbook.Close…
- VBA Find: Search Excel Ranges and Handle Results SafelyIn Excel VBA, Find is a method of a Range, not a separate object: it searches a specified…
- Excel VBA Copy and Paste Formatting: Code and TipsTo copy only formatting with Excel VBA, copy the source range and use PasteSpecial with xlPasteFormats: sourceRange.Copy, then…
- Excel Macro Goal Seek: Automate a Target-Value SearchAn Excel Goal Seek macro uses VBA’s Range.GoalSeek method to make a formula cell reach a target by…
- Excel VBA Date and Time Stamp: Add Fixed TimestampsTo add a fixed date-and-time stamp with Excel VBA, assign Now to a cell and apply a date-time…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.