Excel - Unhide all sheets

Unhide all sheetsThis little sub quickly unhides all hidden sheets (including xlVeryHidden). I use it frequently as people tend to hide worksheets which are no longer needed rather than just delete them, also especially useful when trying to dissect other peoples creations.

It also has some sneaky uses, I regularily come across people who like to protect the content of their macros and spreadsheet from prying eyes. Usually they make the fatal mistake of making the visibility of tabs they don't want people to see as xlSheetVeryHidden, then password protect the macros. What a mistake indeed, the function below gets straight around this:

Sub unhide_all_sheets()
Dim wksht As Worksheet
For Each wksht In ActiveWorkbook.Worksheets
Sheets(wksht.Name).Visible = True
Next wksht
End Sub

I'm not even using an exploit for this, its just plain old user error - whist password protecting the macros blocks UI access to the sheet properties in Visual Basic, it does not lock access to the field as a command.

To properly protect the required tabs the user must Protect Workbook. Not that even this will stop someone who really wants to gain access ;-)

Share page

Subscribe to newsletter

Search Posts

Back to top