46

I was messing around in the sheet settings and somehow managed to make a sheet 'very hidden', but I cannot seem to make it visible again. How can I make it visible?

I am using Excel 2010.

0
42

One option is to do it with VBA

Try out the below:

Sub UnHide()
Dim ws As Worksheet
For Each ws In Sheets
    ws.Visible = True
Next
End Sub

This will show up ALL sheets that are hidden, or very hidden

8
  • 5
    Yeah, Very Hidden can only be unhidden with VBA.
    – BruceWayne
    Sep 28 '17 at 13:30
  • 3
    No, that's wrong. See my reply.
    – NiklasJ
    Sep 28 '17 at 15:23
  • 9
    @NiklasJ The code is not wrong; this will work to set them to visible.
    – TylerH
    Sep 28 '17 at 15:40
  • 8
    @NiklasJ - this code will unhide all sheets in one go, your method will unhide one sheet at a time. Neither is wrong, both are correct. This is the method I would have chosen because I didn't even know your method existed! TIL!
    – FreeMan
    Sep 28 '17 at 16:48
  • 11
    @TylerH I think Nilklas was referring to the implicit statement that you must use code to unhide the cells. His answer does not rely on code.
    – jpaugh
    Sep 29 '17 at 3:35
60

You do it like this:

  1. Open VBA editor (Alt+F11)
  2. Open the VBAProject corresponding to your file.
  3. Open the "Microsoft Excel-objects" folder
  4. Select the Sheet you've hidden.
  5. Go to the properties (press F4)
  6. Change the property "Visible" to xlSheetVisible instead of xlSheetVeryHidden
0
15

In VBA editor, go to the sheet properties and change the below property

enter image description here

5
  • 3
    this is the same method as NiklasJ above ?
    – user775305
    Sep 29 '17 at 11:34
  • 2
    I just noticed, yes it is the same but with picture.
    – Scorpion99
    Sep 29 '17 at 11:40
  • You should have edited @NiklasJ's answer and added the picture... Sep 26 '18 at 12:44
  • I accidentally hid the only sheet in a workbook! I tried the above, saved the spreadsheet, and tried to open it, and it (entire workbook) is not visible. Any ideas? Nov 16 '18 at 16:17
  • Ha! I had another spreadsheet open, and that is why I couldn't see anything. I closed all Excel workbooks, then opened it, nothing was visible but the "View" toolbar had an unhide option enabled! Nov 16 '18 at 16:23
1

To keep them hidden, but visible in the options menu (UnHide..), use the same code but with this..

Sub StillHide()
Dim ws As Worksheet
For Each ws In Sheets
    ws.Visible = xlSheetHidden  <-- change from Visible to this.
Next
End Sub

That way the tabs are not all of a suddenly showing to the user. They are still "hidden".

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy