Hide (or Unhide) A Worksheet - VBA Code Examples - Automate Excel

In this Article

• Hide Sheet in VBA
• Hide Worksheet Tabs
• Unhide Sheet
• Very Hidden Sheets
  • Unhide Very Hidden Sheets
• Hide / Unhide Sheets in the VBA Editor
• Hide All Worksheet Tabs
• Hide / Unhide Sheets in Protected Workbook
• Unhide All Sheets
• VBA Coding Made Easy

This tutorial will teach you how to hide and unhide Worksheets with VBA.

Hide Sheet in VBA

To hide a Sheet in VBA, use the worksheet Visible property.

Either set the Visible property to FALSE:

Worksheets("Sheet1").visible = False

or set the Visible property to xlSheetHidden:

Worksheets("Sheet1").visible = xlSheetHidden

This is the same as if the user right-clicked the worksheet tab and selected “hide”.

Hide Worksheet Tabs

Instead, To hide the entire Worksheet tab area, set the DisplayWorkbookTabs property to False:

Sub Hide_Tabs

ActiveWindow.DisplayWorkbookTabs = False

End Sub

Unhide Sheet

To unhide a Sheet in VBA, use the worksheet Visible property:

Worksheets("Sheet1").Visible = True

or

Worksheets("Sheet1").Visible = xlSheetVisible

Hidden Sheets can be seen by right-clicking in the Worksheet tab area:

Very Hidden Sheets

The Sheet Visible property has a third option: xlSheetVeryHidden:

Worksheets("Sheet1").Visible = xlSheetVeryHidden

Very hidden Sheets are hidden when right-clicking in the Worksheet tab area:

This code will prevent the spreadsheet user from seeing the Worksheet tab at the bottom of the screen. It also hides the Worksheet from the user when they right-click the tabs at the bottom. The only way to see that the Worksheet exists (or unhide the Worksheet) is by opening the Visual Basic Editor.

Unhide Very Hidden Sheets

Very hidden Worksheets are made visible just like regular hidden Worksheets:

Worksheets("Sheet1").Visible = True

or

Worksheets("Sheet1").Visible = xlSheetVisible

Hide / Unhide Sheets in the VBA Editor

You can also toggle the Worksheet Visible property within the VBA Editor:

Hide All Worksheet Tabs

You might also want to hide the Worksheet Tab area altogether to prevent the user from navigating to different worksheets. Learn more about hiding worksheet tabs.

Hide / Unhide Sheets in Protected Workbook

Your workbook must be unprotected before you can hide or unhide worksheets.  To unprotect your workbook structure use the following code:

ActiveWorkbook.Unprotect

If your workbook structure is password-protected you must do this instead:

ThisWorkbook.Unprotect "password"

Unhide All Sheets

This procedure will unhide all worksheets in a workbook, using a For Each Loop:

Sub Unhide_All_Sheets() Dim ws As Worksheet ActiveWorkbook.Unprotect For Each ws In Worksheets ws.Visible = xlSheetVisible Next End Sub

Notice that we first unprotect the workbook, just in case it was password protected.

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro – A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!

Learn More!