Hi,
You should be hiding the sheets in the BeforeSave event handler, not in the BeforeClose event handler. There are two reasons for this:
(1) If you use the BeforeClose event, if the current user ("User1") saves their progress and continues to use the workbook, if another person decides to open the workbook ("User2"), they would be able to open it with macros disabled and see all of the sheets. This is because all of the sheets were visible when User1 saved the workbook - they won't be hidden again until User1 closes the workbook.
(2) If you use the BeforeClose event, your users have no way to close the workbook without saving. What if they have made some changes and then decided that they don't want to save them?
These two issues make the BeforeSave event handler a better choice. When the user saves the workbook, you need to hide the relevant sheets, save the workbook and then unhide the relevant sheets again so that the user can continue to use them.
Assuming that the name of the sheet you want visible if macros are disabled is "Special Note", the code would be something like this:
ThisWorkbook class module
Option Explicit
Const strDASHBOARD As String = "Special Sheet"
Private Sub Workbook_Open()
UnHideSheets
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
On Error GoTo ErrorHandler
Application.ScreenUpdating = False
Application.EnableEvents = False
HideSheets
Me.Save
UnHideSheets
Me.Saved = True
ErrorExit:
Cancel = True
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub
ErrorHandler:
MsgBox "Error Number: " & CStr(Err.Number) & vbNewLine & _
"Error Description: " & Err.Description
Resume ErrorExit
End Sub
Private Sub UnHideSheets()
Dim objSheet As Object
For Each objSheet In Sheets
objSheet.Visible = xlSheetVisible
Next objSheet
Sheets(strDASHBOARD).Visible = xlSheetVeryHidden
End Sub
Private Sub HideSheets()
Dim objSheet As Object
Sheets(strDASHBOARD).Visible = xlSheetVisible
For Each objSheet In Sheets
If objSheet.Name <> strDASHBOARD Then
objSheet.Visible = xlSheetVeryHidden
End If
Next objSheet
End Sub
Note that the set up I've posted does not give the user the option to SaveAs.
Bookmarks