Hiding an Excel worksheet with VBA

Question

I have an Excel spreadsheet with three sheets. One of the sheets contains formulas for one of the other sheets.

Is there a programmatic way to hide the sheet which contains these formulas?

Kratzer

Answer 1

To hide from the UI, use Format > Sheet > Hide

To hide programatically, use the Visible property of the Worksheet object. If you do it programatically, you can set the sheet as "very hidden", which means it cannot be unhidden through the UI.

ActiveWorkbook.Sheets("Name").Visible = xlSheetVeryHidden 
' or xlSheetHidden or xlSheetVisible

You can also set the Visible property through the properties pane for the worksheet in the VBA IDE (ALT+F11).

Cumbrous

@MárioMeyrelles Not on a computer with Excel so I can't try out myself - what is very hidden?! – Machicolation

it means you can't unhide it from the ribbon (format > hide & unhide > unhide sheet). You can only unhide it via VBA. – Cumbrous

Answer 2

You can do this programmatically using a VBA macro. You can make the sheet hidden or very hidden:

Sub HideSheet()

    Dim sheet As Worksheet

    Set sheet = ActiveSheet

    ' this hides the sheet but users will be able 
    ' to unhide it using the Excel UI
    sheet.Visible = xlSheetHidden

    ' this hides the sheet so that it can only be made visible using VBA
    sheet.Visible = xlSheetVeryHidden

End Sub

Deedeeann

Answer 3

Just wanted to add a little more detail to the answers given. You can also use

sheet.Visible = False

to hide and

sheet.Visible = True

to unhide.

Source

Rocca

Is this a hidden or very hidden way? – Deforest

Well, combining msdn.microsoft.com/en-us/VBA/Excel-VBA/articles/… and learn.microsoft.com/en-us/dotnet/visual-basic/…, it looks like True and xlSheetVisible are both -1, and False and xlSheetHidden are 0. This implies that sheet.Visible = False is equivalent to sheet.Visible = xlSheetHidden. – Rocca

Answer 4

This can be done in a single line, as long as the worksheet is active:

ActiveSheet.Visible = xlSheetHidden

However, you may not want to do this, especially if you use any "select" operations or you use any more ActiveSheet operations.

Ingurgitate

Answer 5

I would like to answer your question, as there are various methods - here I’ll talk about the code that is widely used.

So, for hiding the sheet:

Sub try()
    Worksheets("Sheet1").Visible = xlSheetHidden
End Sub

There are other methods also if you want to learn all Methods Click here

Arapaima