I have a file that when the file is being saved needs to have a sheet hidden prior to the save and then made visible again.
This is required as the sheets is accessed via a password on a user form and if made visible by the user, needs to be hidden in case they forget to hide it.
I can't use the 'Workbook_Open' event to hide the sheet upon opening because a user may open the file without macros enabled and the sheet would be visible.
The problem is that I am using the following
I am getting a loop where I am being asked if I want to save the file as changes have been made. The user may want to save the changes when closing and when the save in the above code has executed, I just need the file to close.
TIA
This is required as the sheets is accessed via a password on a user form and if made visible by the user, needs to be hidden in case they forget to hide it.
I can't use the 'Workbook_Open' event to hide the sheet upon opening because a user may open the file without macros enabled and the sheet would be visible.
The problem is that I am using the following
Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim strActiveSheet As String
If ActiveWorkbook.Saved = True Then
Application.DisplayAlerts = False
Cancel = True
Exit Sub
Else
End If
If Sheets("Fixed Labour").Visible = True Then
strActiveSheet = ActiveSheet.Name
Sheets("Fixed Labour").Visible = xlVeryHidden
Application.EnableEvents = False
ActiveWorkbook.Save
Application.EnableEvents = True
Sheets("Fixed Labour").Visible = True
Sheets(strActiveSheet).Select
Cancel = True
ActiveWorkbook.Saved = True
Else
Application.EnableEvents = False
ActiveWorkbook.Save
Application.EnableEvents = True
End If
End Sub
TIA