Disable Save Notification Upon Closing Excel

[Dschwarz]

I created a spreadsheet that uses VBA code to simplify the task of performing a series of complex calculations with the least amount of user input and the simplest possible user interface. I use a macro to hide the menu ribbon and any non-essential features upon opening the file. Upon closing the application the standard user interface settings for excel are restored.

The user is also prevented from saving the excel file and the data fields are set to default values every time the file is opened. To save the data entries, I have created a simplified menu with a couple of buttons for storing and recalling data in the form of a text file. With the simplified interface the user can close the file by clicking the red "X' in the corner of the window. Before the application closes, a reminder to save the text file appears so the data is not lost upon closing.

The problem is that upon closing, in addition to my reminder message to save the data into a text file, excel is asking the user to save the Excel file as well (this is what I am trying to suppress). I found by accident that if the file is saved to one drive, this request to save the excel file does not occur and only my message appears before closing. Below are things I have tried (including unchecking all auto save options as shown in the picture below) but I cannot disable the save prompt when saving locally (Any suggestions would be appreciated):


Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Test if calculator exit button was pressed accidentally before saving data
If MsgBox("Exit calculator now? (any unsaved data will be lost)", vbQuestion + vbYesNo + vbDefaultButton2, _
"Exit Calculator") = vbNo Then
Cancel = True
Exit Sub
Else
ThisWorkbook.Saved = True
End If
'Restore standard user interface settings before the workbook is closed
Call UIShow
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Sets save status to true
'ThisWorkbook.Saved = True
'Disable save function
'Cancel = True
End Sub

31288

31289

[Aussiebear]

Have you tried this?



Sub CloseBook()
Application.DisplayAlerts = False
ActiveWorkbook.Close savechanges: = True
Application.DisplayAlerts = True
End Sub

[Dschwarz]

Well, this is ultimately suppressed the save notification. Thanks for the input!


Private Sub Workbook_BeforeClose(Cancel As Boolean)

'Test if calculator exit button was pressed accidentally before saving data
If MsgBox("Exit calculator now?" & vbCr & "(any unsaved data will be lost)", vbQuestion + vbYesNo + vbDefaultButton2, _
"Exit Calcuator") = vbNo Then
'Cancel the call to close the application if user inputs "no"
Cancel = True
Else
'Suppress excel alert messages
Application.DisplayAlerts = False
'Set save status to true
ThisWorkbook.Saved = True
'Close application
Application.Quit
End If
End Sub