36

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?

Teamothy
  • 2,000
  • 3
  • 16
  • 26

5 Answers5

70

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

Tmdean
  • 9,108
  • 43
  • 51
22

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
Dirk Vollmar
  • 172,527
  • 53
  • 255
  • 316
19

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

trejder
  • 17,148
  • 27
  • 124
  • 216
Charles Wood
  • 864
  • 8
  • 23
  • Is this a `hidden` or `very hidden` way? – Ans Jan 09 '18 at 09:08
  • 1
    Well, combining https://msdn.microsoft.com/en-us/VBA/Excel-VBA/articles/xlsheetvisibility-enumeration-excel and https://learn.microsoft.com/en-us/dotnet/visual-basic/language-reference/data-types/boolean-data-type, 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`. – Charles Wood Jan 09 '18 at 17:44
6

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.

Andrew Scagnelli
  • 1,584
  • 4
  • 18
  • 26
0

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

Priya Sharma
  • 1
  • 2