VBA – Hide (or Unhide) a Worksheet

Associated Files Download Links

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”.

vba hide sheet

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:

vba unhide sheet

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:

vba very hidden sheet

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:

vba visible property

 

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!

alt text

 

Learn More!


<<Return to VBA Examples