It is easy to hide a sheet, you right click the sheet tab and select Hide. Unfortunately, it is just as easy to unhide that sheet once hidden. If you want to hide sheets and make it harder to unhide them, then these macros may help.
In a previous post (link below) I used message boxes to ask whether a sheet should be hidden.
The macro techniques below are more automated and based on the sheets you select.
There are four separate macros. Some use the xlVeryHidden setting (yes that is a real setting). This stops the user right clicking a sheet tab to unhide it.
The first, and easiest to understand macro is the UnHideAllSheets macro.
Sub UnHideAllSheets() ' This macro unhides all sheets Dim ws As Worksheet For Each ws In Worksheets ws.Visible = True Next ws Set ws = Nothing End Sub
This macro loops though all the worksheets and makes them visible. This makes testing the other macros a lot easier and it is useful when maintaining a file with lots of hidden sheets.
The second macro HideAllOtherSheets is used when you want to select a single sheet and hide all the other sheets.
Sub HideAllOtherSheets() ' This macro hides all but the active sheet Dim ws As Worksheet For Each ws In Worksheets If ws.Name <> ActiveSheet.Name Then ws.Visible = xlSheetVeryHidden Next ws Set ws = Nothing End Sub
This macro loops though all the worksheets and hides all but the ActiveSheet.
The third macro HideUnSeletectedSheets allows you to multi-select sheets using the Ctrl key and then hide all the other sheets. You select the sheets you want to remain visible, and the rest will be hidden.
Sub HideUnSelectedSheets() ' This macro hides the sheets that aren't selected Dim ws As Worksheet Dim strNames 'capture all selected sheet names For Each ws In ActiveWindow.selectedSheets strNames = strNames & "," & ws.Name & "," Next ws 'check if a sheet is selected - if not hide it For Each ws In Worksheets If InStr(strNames, "," & ws.Name & ",") = 0 Then ws.Visible = xlSheetVeryHidden Next ws Set ws = Nothing End Sub
This loops through all the selected sheets and creates a text string of all the names. The next loop then checks if each sheet name is in that text string – if it isn’t it is hidden.
Warning: Multi-selecting sheets is dangerous. You may forget you have multiple sheets selected and every change you make to one sheet is made in ALL the other sheets. Make sure you unselect the sheets by right clicking a sheet tab and selecting Ungroup Sheets.
The last macro HideSelectedSheets hides the selected sheets. Since it is possible to select all the sheets, and at least one sheet must be visible, this macro includes On Error Resume Next to ignore the error if all sheets are selected. The last sheet will remain visible if that is the case.
Sub HideSelectedSheets() ' This macro hides the selected sheets Dim ws As Worksheet 'at least one sheet must be visible ' the code below will ignore the error ' if all sheets are selected ' the last sheet will remain visible On Error Resume Next 'hide all selected sheets For Each ws In ActiveWindow.selectedSheets ws.Visible = xlSheetVeryHidden Next ws Set ws = Nothing End Sub
If you are unsure how to use macros check out the blog post below.