Let's talk about how to hide sheets, as well as how to show hidden sheets in Excel , including how you can display very hidden sheets using Visual Basic.
Greetings to all, dear readers of the TutorExcel.Ru blog.
From time to time, the need arises to hide sheets in Excel either from the prying eyes of other users, or from ourselves, so that these same sheets do not distract or interfere with work.
The opposite need also arises - for partial or complete display of hidden sheets. In general, let's look at different ways to implement both hiding and showing sheets.
How to show a hidden sheet in Excel?
To open the hidden sheet back, we also right-click on any of the sheets in the bottom panel and select the Show . In the window that appears, you can see all the invisible sheets of the book (even if there is only one) and select which sheet you want to display:
We've sorted out hiding and displaying sheets, let's move on to a slightly more complex task, namely, learn how to hide a sheet so that an ordinary user cannot display it.
Completely hiding sheets in Excel (super hidden)
As we have already outlined above, the problem with simply hiding sheets in Excel is that everyone who has access to the document can display them. If this action is performed not only to bring aesthetic beauty to the book, but also to protect data, you will need to perform several additional actions, having first activated the developer mode in the program, if this has not been done previously.
- Open the File menu and go to Options.
- In the window that appears, click “Customize Ribbon” and check the “Developer” checkbox located in the “Main Tabs” block. Click "OK" to confirm your changes and close the settings.
How to make a very hidden sheet in Excel?
We can configure the display parameters of any sheet in Excel using Visual Basic. Go to the Visual Basic editor (on the tab bar, select Developer -> Code -> Visual Basic or use the key combination Alt + F11):
In the upper left part of the window, in the list, select the sheet we need and just below we look in detail at the block with settings (if it is not initially displayed, then in the Visual Basic tab bar we select View -> Properties Window and the block will appear).
Visible property is responsible for displaying the sheet in the parameters, and as we can see, there are only 3 options:
- xlSheetVisible . The sheet is open and displayed in tabs;
- xlSheetHidden . The sheet is hidden but appears in the list of invisible sheets;
- xlSheetVeryHidden . The sheet is hidden and not visible anywhere.
Thus, to hide a sheet using Visual Basic we specify the value of the xlSheetHidden , and to show it we set xlSheetVisible .
from the name of the last value xlSheetVeryHidden , but in principle you can also often find the expression super hidden. At the same time, which is very important, you can set the value of the xlSheetVeryHidden only through Visual Basic, which does not allow the average user to detect very hidden sheets in the standard way.
Let's return to our example.
Since we hid Sheet1 , its visibility is set to xlSheetHidden , and now we are interested in option 3. We select it and now the list of hidden sheets is empty (the button in this case is not active, although we know that there are actually very hidden sheets in the book):
Now the user will not see such a super hidden sheet in the Excel interface, but he can still find it if he goes into the Visual Basic editor.
If the task is to find such sheets and display them, then we repeat similar steps and in Visual Basic for such sheets we set the xlSheetVisible , after which all sheets will become visible.
Super hidden sheet
162996 21.10.2012
Sometimes some sheets in the book have to be hidden from the eyes of users. The classic method involves hiding the sheet through the menu Format - Sheet - Hide or right-clicking on the sheet tab - Hide :
The problem is that the user, by going to the Format - Sheet - Display or right-clicking on the shortcut of any sheet and selecting Unhide , will see the names of the hidden sheets and understand that some of the information is hidden from him:
Therefore, it is better to make sure that the user does not even realize that there are any hidden sheets in the book. To do this, open the Visual Basic editor:
- in Excel 2003 and older - by selecting Tools - Macro - Visual Basic Editor
- in Excel 2007 and later - by clicking the Visual Basic Editor on the Developer or by pressing ALT+F11
We are looking for this window on the screen:
If it is not visible, then you can display it through the menu View - Project Explorer (top part) and View - Properties Window (bottom part).
In the upper part of the “tree” we find and select our sheet (in the picture - Sheet1), and in the lower part we find the Visible (at the end of the list) and make it xlSheetVeryHidden .
Voila! Now you can see this sheet and find out about its existence only in the Visual Basic editor and it will not be displayed in any Excel windows or menus. The less you know the better you sleep. 
Related links
- 4 levels of protection in Excel files
- Hiding Sheets in Bulk Using the PLEX Add-in
Volna
21.10.2012 13:55:05
I hid the sheet, but now the macro that uses it doesn’t see it. Please tell me what needs to be written so that the macro finds the hidden sheet? Link
squadra
21.10.2012 13:55:37
Sheets("List").Visible = -1 macro text Sheets("list").Visible = 2 i.e. we first show the sheet, then we hide it..... Parent Link
Alexander
21.10.2012 13:56:30
Good afternoon Perhaps I didn’t find it.. Sometimes the macro hides the sheets.... then to display it you need to do a lot of unnecessary movements (format-sheet-display) and so on 20 times in a row…. Is it possible to simplify this somehow? Link
Alexander
21.10.2012 13:57:09
Here is a macro that opens all hidden sheets in the workbook (except those hidden by the VeryHidden method)
| Sub UnHideAllSheets() Dim x As Integer x = 0 MsgBox ("In the Workbook of Everything" + Str(Application.ActiveWorkbook.Sheets.Count) + "sheet(s)") For Each sh In ActiveWorkbook.Worksheets If sh.Visible = False Then x = x + 1 If sh.Visible = False Then sh.Visible = True Next If x = 0 Then MsgBox (“Looks like there are no hidden sheets in the book, be paranoid!”) If x > 0 Then MsgBox (“Open” + Str(x) + » hidden sheet(s)") End Sub |
Parent Link
user
21.10.2012 13:57:50
Alex, thank you very much!! very useful thing =) Parent Link
Orxan
21.10.2012 13:58:38
Thank you that this is all free. Link
Nikolay Pavlov
21.10.2012 13:59:44
Parent Link
Vladimir Kapitonov
24.01.2016 21:59:57
A slightly expanded macro from Alexander, should show all hidden sheets.
| Sub UnHideAllSheets() Dim x As Integer Dim sh As Worksheet x = 0 MsgBox ("In the Workbook of Everything " + CStr(Application.ActiveWorkbook.Sheets.Count) + " sheet(s)") For Each sh In ActiveWorkbook.Worksheets If sh .Visible = False Then x = x + 1 If sh.Visible = xlSheetVeryHidden Or _ sh.Visible = xlSheetHidden Then x = x + 1 sh.Visible = xlSheetVisible End If Next If x = 0 Then MsgBox (“Looks like there is no hidden sheets, be paranoid!”) If x > 0 Then MsgBox (“Open” + CStr(x) + “hidden sheet(s)”) End Sub |
Link
Sobir Mazhidov
29.06.2019 17:43:31
How is it different from Alexander's code? Parent Link
Ruslan
27.03.2017 12:59:32
People, how can you open it again? Link
Simon Svetlana
08.11.2017 01:05:22
Indeed, I am interested in the question of how to open the hidden sheet again. The above macros show that there is a hidden sheet, and I know which sheet it is. But how to open it again? through View – Properties Window and Format – Sheet – Display
doesn't fit either. Link
Nikolay Pavlov
08.11.2017 01:11:03
Just as you hid it, bring it back. Open the Visual Basic editor (Alt+F11), select the super-hidden sheet in the Project Explorer panel (all sheets are always visible there) and then in the Properties panel change its Visible property back to xlSheetVisible. Parent Link
Simon Svetlana
08.11.2017 01:26:13
It says that the Visible property of the Worksheet class cannot be set. The protection from the sheet and book has been removed. Link
Nikolay Pavlov
08.11.2017 09:29:29
Strange. Svetlana, if it’s possible to send the file to me by email for vivisection, I’ll take a look. Parent Link
Simon Svetlana
08.11.2017 09:35:42
Okay, I’ll send it to you - it’s most interesting, either I don’t understand something, or there really is a problem. Link
Dmitry B
26.11.2017 22:49:08
I came across the fact that a super-hidden sheet is displayed in the Book Information window in the Book Protection section. All protected sheets are displayed there, including super-hidden ones if they have a password. Conclusion - we don’t put a password on the super-hidden sheet. Link
Dmitry Vaitkevicius
06.12.2017 13:16:41
Good afternoon Is it possible to make some sheets super-hidden, without listing all the necessary ones and assigning an attribute to each one separately? For example, only those that have a range (Name) from Sheet22 to Sheet33? Thank you! Link
Nikolay Greynert
24.12.2017 18:57:00
I also get the message that the Visible property of the Worksheet class cannot be set. What solution? Link
Kirill Kritsky
17.12.2018 17:48:06
Good evening. Please help me finish the code. The problem is that after the macro runs and the copied “Order Form for Customer” sheet opens, it is not possible to hide the sheet back in the original file. And it is necessary that the hidden one remains hidden, but in this case the macro on the file stops working. In general, everything works, except for this moment.
| Sub Print_Button1_Click() On Error Resume Next Sheets("Order Form for Customer").Visible = 1 Const REPORTS_FOLDER = "Orders\For Customers" MkDir ThisWorkbook.Path & "\" & REPORTS_FOLDER ChDrive Left(ThisWorkbook.Path, 1): ChDir ThisWorkbook.Path & "\" & REPORTS_FOLDER Filename = Application.GetSaveAsFilename("Calculation for customer Order No. .xls", "Excel workbook (*.xls),", , _ "Enter the file name for the saved report", "Save "") If VarType(Filename) = vbBoolean Then Exit Sub Err.Clear: Worksheets("Order Form for Customer").Copy: DoEvents If Err Then Exit Sub If ActiveWorkbook.Worksheets.Count = 1 And ActiveWorkbook.Path = "" Then ActiveWorkbook.SaveAs Filename, xlOpenXMLWorkbook Application.DisplayAlerts = False Sheets("Customer Order Form").Visible = 2 End If End Sub |
Link
How to hide a sheet?
If a sheet stores data that will be needed in the future or is used for calculations on other sheets, but it interferes with work or you simply don’t need it to be displayed in the list of sheets, then you can simply Hide . To do this, right-click on the desired sheet - Hide . The sheet will not be visible in the program window, but it will be saved.
To show hidden sheets , you need to right-click - Show .
All hidden sheets will be shown again in the panel.
