Unable to Suppress Save Prompt

[davidam]

Hello Everyone,
I am trying to prevent the save prompt when the 'X' at the top right of the sheet is used to close a file. I have all the usual stuff and I have been trying to embellish it with extra event enabling and extra Alerts = False etc, but nothing will prevent the prompt if the X is used. The following code is situated in Excel Objects: "ThisWorkbook". Should I put this code onto each sheet? There are several in this particular workbook. Any help would be greatly appreciated.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayAlerts = False
Application.EnableEvents = True
ThisWorkbook.Saved = True
Application.DisplayAlerts = True
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.EnableEvents = True
Application.DisplayAlerts = False
ThisWorkbook.Saved = True
Cancel = True
Application.DisplayAlerts = True
End Sub

Thanks in advance,
David

 

[6StringJazzer]

I cannot reproduce your problem. When I used your code, change the file, then X out, it closes without a prompt.

Also, if you are cancelling the save any time the user tries to save it, then Saved is going to be True and you don't need to avert a prompt with BeforeClose. Yet, somehow you are getting a prompt anyway. I don't know why.

If you move this code anywhere else it will have no effect at all. It will be called only if it is in ThisWorkbook.

Are you absolutely certain that

The following code is situated in Excel Objects: "ThisWorkbook".

 

[Micron]

I tried you code. No prompt for me 3x. However, changes are not saved. Not sure if that is your intent.

 

[davidam]

I cannot reproduce your problem. When I used your code, change the file, then X out, it closes without a prompt.

Also, if you are cancelling the save any time the user tries to save it, then Saved is going to be True and you don't need to avert a prompt with BeforeClose. Yet, somehow you are getting a prompt anyway. I don't know why.

If you move this code anywhere else it will have no effect at all. It will be called only if it is in ThisWorkbook.

Are you absolutely certain that

Yes, absolutely certain. I am starting to think there may be some corruption of the file that is causing this not to work. It is an old file that I have been working on for years. I might just start fresh and import all the sheets into a new file. I did this once before and it solved a different issue with a different program.

 

[davidam]

I tried you code. No prompt for me 3x. However, changes are not saved. Not sure if that is your intent.

Yes, my intent is to not save any changes. I want the file to respond to new data being pasted in each time it is accessed.