You might want to hide or unhide certain sheets in a workbook for security reasons and there are several ways you can do this. In this article, we're going to show you how to unhide sheets in Excel with VBA.

How to unhide sheets in Excel

Worksheets are objects in the workbook's worksheet collection and they have a Visible property. The Visible property determines the visibility status of a worksheet, as the name suggests. This property can take 3 values:

• xlSheetHidden
• xlSheetVeryHidden
• xlSheetVisible

xlSheetVisible and xlSheetHidden values specify a "natural" visible / hidden status, where the user can change the status by right-clicking the worksheet name, and select Hide / Unhide. On the other hand, you can use the xlSheetVeryHidden option to prevent any user interaction with the hidden worksheet.

You can do this in two ways:

• Module
• Immediate Window

In the Module method, you need to add the module into the workbook or the add-in file. Copy and paste the code into the module to run it. The main advantage of the module method is that it allows saving the code in the file, so that it can be used again later. Furthermore, the subroutines in modules can be used by icons in the menu ribbons or keyboard shortcuts. Remember to save your file in either XLSM or XLAM format to save your VBA code.

The Immediate Window method, on the other hand, is essentially a quick and dirty method where you can simply copy and paste the code into the Immediate Window, and press the Enter key to run it. Unfortunately, any code you use in the Immediate Window will not be saved. Also note that icons and keyboard shortcuts will not be available.

Unhide all worksheets

Module Version:

Sub UnhideAllSheets()
    Dim sh as Worksheet
    For Each sh In ActiveWorkbook.Worksheets
        sh.Visible = True
    Next sh
End Sub

Immediate Window version:

For Each sh In Worksheets: sh.Visible = True: Next sh

Unhide a specific worksheet

Module Version:

Sub UnhideSpecificWorksheet()
    Worksheets("Sheet1").Visible = True
End Sub

Immediate Window version:

Worksheets("Sheet1").Visible = True