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):
Save Settings.jpgPrivate 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
Save Message.jpg