Put this in your Workbook BeforeSave Event. This code will loop through a
collection of worksheets that you specify and hide them. Just change the
sheet names to fit your application.
Note: At least one sheet in the workbook must be visible at ALL times or
VBA will throw an error. Plus, do you want the sheets "Hidden" or "Very
Hidden"?
"Hidden" will allow users to unhide work sheets by right clicking the sheets
tabs at the bottom left and selecting the sheet they want to unhide.
"Very Hidden" will only allow the sheet to become unhidden through code
using VBA.
Hope this helps! If so, let me know, click "YES" below.
Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim colSheetsToHide As Collection
Dim wks As Worksheet
' collection of sheets to hide when workbook is saved
Set colSheetsToHide = New Collection
With colSheetsToHide
.Add Sheets("Sheet1")
.Add Sheets("Sheet2")
.Add Sheets("Sheet3")
End With
' hide worksheets in collection
For Each wks In colSheetsToHide
wks.Visible = False
Next wks
End Sub
