It is easy to hide multiple sheets in Excel. Unfortunately, it now just as easy to unhide those sheets. You can hide sheets and make it harder to unhide them. You can use a setting called xlVeryHidden (no kidding) that won’t display the sheet name if you right click a sheet tab and choose unhide sheets.

Typically to change this setting (or property) you’ve got to go into the VBA window. This is repetitive and if you’ve got a lot of sheets to hide, time consuming.

Over the years I’ve written a few blog posts about hiding and unhiding sheets. Here’s yet another flexible technique.

The macro below will apply the xlVeryHidden setting and it will ask you for each sheet. All you need to do is click Yes to hide the sheet and No if want it visible. If in doubt, click No and then you can re-run the macro if you need to.

Note this doesn’t make it impossible to unhide the sheet. It just makes it harder to unhide the sheet. Most Excel users are not aware of this setting and so they won’t even look for extra sheets if they don’t see any sheets listed when they choose unhide.

Another Note you must leave at least one sheet visible.

The macro that does the work is shown below.

Sub HideAllSheetsAsk()
Dim ws
For Each ws In Worksheets
  If MsgBox("Hide " & ws.Name & " ?", vbQuestion + vbYesNo, "Hide sheet?") = vbYes Then
      ws.Visible = xlVeryHidden
  Else
      ws.Visible = True
  End If
Next ws
End Sub

This macro goes through every worksheet in the file, and it asks you whether to hide it using a message box dialog. Based on your response, the sheet is either hidden or it is displayed.

If you are not familiar with macros, then the link below can help you install and use the above macro.

How to make the most of Excel Macros