Suppress 'Save Changes' Dialog

[mikeymay]

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

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

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

 

[RedBeard]

But if the user opens the file with macros disabled, the above won't work anyway?

 

[Joe4]

I have seen users created workbooks where all the sheets are hidden/protected by default, and the only why to unhide/unprotect stuff is to enable macros (there is code in the Workbook_Open event that unhides/unprotects the data). So that is one way to force the users to enable macros. Otherwise, if they don't they really cannot see or do anything.

 

[mikeymay]

When the file is saved the sheet is hidden so if another user opens it they can't see the sheet.

What I need to achieve is when the file is saved and the sheet has been made visible, it is saved with the sheet hidden and then made visible again. If the sheet isn't visible when saved then nothing needs to happen.

I don't need to implement hiding ALL sheets unless macros are enabled as there is only one sheet that needs to be hidden to users who are not allowed to see it.

I just need to suppress the save changes dialog box and I can't seem to the right way to tell Excel that the file doesn't need saving and the save changes dialog box doesn't need to pop up despite there being changes since the last save which is in the Before_Save module.


Thanks

 

[Joe4]

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

My comment is in reference to the comment above and the good point that Redbeard brought up. If you are concerned about the user not enabling macros when opening the file, then any following question regarding VBA code is moot, because that VBA won't run either if macros are not enabled.

So the comments I made were just a way to force users to enable macros, so that the concern you mentioned above them not enabling macros isn't an issue.

By the way, I tried your code and it seems to work fine for me. I don't get any dialog box.

 

[mikeymay]

Thanks for trying the code I posted Joe.

The loop I experience is when I save the file and then close the file, I get the 'save chages?' dialog. If I click yes to save (it is already saved, but the user may think it hasn't), the dialog come up again just before it attempts to close the file. If I click No then the file closes.


Thanks

 

[Joe4]

Note that you could add this code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.DisplayAlerts = False
    ActiveWorkbook.Close
    Application.DisplayAlerts = True
End Sub

But, that may have an unintended affect. If you save the file, but then make other changes, and then try to close the file, it will close the file without the prompt, meaning those changes that you made after saving the file would be lost.