45

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.

41

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

  • 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
15

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

enter image description here

  • 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... – I say Reinstate Monica 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? – Mark Stewart 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! – Mark Stewart 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