Home > Software design >  Excel 2019 still displays save prompt even when the Application.EnableEvents = False
Excel 2019 still displays save prompt even when the Application.EnableEvents = False

Time:12-19

I tried all the different blocks of codes below but when I close the Excel, it still prompts whether to save or not.

I have the code in the Workbook BeforeClose event. I am using excel 2019 and Windows 11

I tried this,

Application.EnableEvents = False
Application.DisplayAlerts = False
ThisWorkbook.Saved = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True

this,

Application.DisplayAlerts = False
ThisWorkbook.Saved = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True

and this,

Application.EnableEvents = False
Application.DisplayAlerts = False
ThisWorkbook.Save
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True

Am I missing something?

CodePudding user response:

Application.Calculation = xlCalculationAutomatic is a workbook change. Hence the previous saved state is no longer valid. You can confirm this easily enough my doing the same thing manually. i.e. toggle calculation state after a save and attempt to close the book.

Interestingly enough, it you save one book with Auto calc on and close it. Then save another with Auto calc off and close it. The calc state will be reflected by whichever you next open. That is to say, it's saved in the workbook (not the application). Excel resolves conflicts by adopting the current state to any subsequent books opened.

This isn't new by the way. Been like that as long as I can remember. Which, unfortunately, is too long :)

  • Related