Shared/Read Only WITHOUT Save prompt

[tbatftc]

Excel 2002 spreadsheet used as a reference guide. I wish to keep readonly
attribute and allow temporary calcs upon open; however, I do not want them to
receive the "Do you want to save changes prompt" when the file is closed
since I don't want their changes saved anyway. Can I suppress the prompt
without saving? Thank you!

 

[Bob Umlas, Excel MVP]

Right-click the Excel LOGO (near the file menu), select View Code, put this in:

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

 

[tbatftc]

Thank you Bob. Since I am the only one who will make updates on very rare
occasions, I will just remember to remove the code during those times, after
I take off the readonly property.

 

[Gord Dibben]

Unshare the workbook to access the VB Editor.

Place this in Thisworkbook module.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayAlerts = False
ThisWorkbook.Saved = True 'Excel thinks it is saved but is not
Application.DisplayAlerts = True
End Sub

Re-share which will save the workbook with the code.

Close, re-open, make some some changes then close.

The workbook will close with no save changes and no message.

You may want this also to prevent the workbook from being saved by users
before closing.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI _
As Boolean, Cancel As Boolean)
Cancel = True
MsgBox "You are not authorized to save this workbook"
End Sub

You will have to disable events in order to save the workbook once with the
second event code then re-enable events after saving.


Gord Dibben MS Excel MVP