Hi Guys,

I have the following code in my workbook under "this_workbook"
I have a user form where different people have different logins and when they login they have access to different sheets. I would have access to all sheets that I would not like staff to access. I cannot remember the initial problem, but sometimes when I logged out staff could see my sheets when they logged in so someone helped me with the below code so every time anyone closed the workbook all sheets are hidden first then it shuts down, this fixed the error where the person coming after me could only see the splash sheet while they logged in then only gained access to their own sheets.

The issue I am having is, once a week just to be cautious I save a backup of this workbook on a separate drive on the computer by using "save as". (I have no access to cloud backup in work) This works fine and I "save as" worksheet 1 (backup) then I close it and all sheets hide. However when I go to open the original worksheet 1 all previous sheets are still open. I know this is because by using the "save as" the original one never got to close properly therefore the below code never kicked in.

Any suggestions how to work around this? Now that I know it happens I always go straight back to the original open it and close it too but that's a bit of a clunky workaround


Private Sub Workbook_BeforeClose(Cancel As Boolean)

   '// Even though I have changed the call to the new Hide procedure,
   '// is this really necessary? The sheets are hidden in the Save event
   '// and to change the State of the workbook will set the Dirty flag
   '// causing the Save prompt to appear...
   
   ' Hide all sheets except the splash sheet
   SheetHide True
   
   'Stop all timers
   On Error Resume Next
   Application.OnTime RunWhen, "SaveAndClose", , False
   Application.OnTime RunStatusBarWhen, "TimeTilExitTimer", , False
   On Error GoTo 0

   'Disable 'Save Cancel' if Called from Here
   bGblDoNotCancelIfCalledFromCloseEvent = True

   'Clear the Status Bar
   Application.StatusBar = ""
  
End Sub
Thanks in advance