Disabling the "want to save your changes?" dialogue box when closing out of Excel

[ktielens]

I have a workbook that is stored as read only on a SharePoint site. Users are unable to save the document (save as is allowed), but when closing out of the document, the dialogue box still shows saying "Want to save your changes to [file name]" with options of "save" "don't save" and "cancel".

Is there a way to disable this box from appearing? It only serves to confuse the users. Upon closing I would like the document to close without any kind of save or notification regarding saving.

Thank you in advance.

 

[Roderick_E]

Application.DisplayAlerts = False
'close
Application.DisplayAlerts = True

 

[shg]

I don't see why it should be confusing -- if they choose Save, they will see the SaveAs dialog. Better that than fat-fingering Close and losing work, IMO.

 

[ktielens]

Application.DisplayAlerts = False
'close
Application.DisplayAlerts = True

Thank you Roderick. Would I add that as a workbook "beforeclose" statement or in a new module of the project?

 

[Roderick_E]

more details from MS site: https://support.microsoft.com/en-us/kb/213428

Thank you Roderick. Would I add that as a workbook "beforeclose" statement or in a new module of the project?

 

[Misca]

Add the following to the ThisWorkbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

ThisWorkbook.Saved = True


End Sub

The code does not save the workbook; it just makes it look like it's saved.

 

[ktielens]

The above is not working or maybe I just don't understand where to put it in the VBA. I've tried putting it in a module as described on the Microsoft site, as well as in a "this workbook" beforeclose. It works (the dialogue box does not show) upon the first close but when I go back in, make some changes and x out the box shows back up again.

 

[ktielens]

Add the following to the ThisWorkbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Saved = True
End Sub

The code does not save the workbook; it just makes it look like it's saved.

Thank you so much for the specific direction (put it in the thisworkbook module). Worked like a charm!!

 

[Misca]

You might want to make the code to check both the path and file name match the original path and file name bef ore the Saved property becomes true. This way the workbook asks about saving if they've already saved it to another location / filename.

 

[ktielens]

You might want to make the code to check both the path and file name match the original path and file name bef ore the Saved property becomes true. This way the workbook asks about saving if they've already saved it to another location / filename.

That's a great idea. Do you have any suggestions regarding where I can look for that type of code? I am a VBA newbie, if it wasn't obvious.