Microsoft Excel - A Challenge, Is it possible?

If an individual selects the red X in the upper right corner of Excel, is it possible to have Excel close without saving any changes?

Dan

I am not talking about opening, I am talking about when a spreadsheet is already open and someone clicks the RED X in the upper right  corner of the spreadsheet.  How can I prevent the program (Excel) from saving changes when it completly closes.  I want it to close without saving changes when the RED X is selected.

How would you assigned the RED X to this code?

 Application.DisplayAlerts = False
 Application.Quit

Ya possible, very simple you make the Excel document to read only or restrict formatting and editing.

If you don't want content reviewers to accidentally modify your document, you can make the document read only before sending it for review. Another method is to restrict formatting and editing.

Making your document a read-only file means that the document can be read or copied but not modified. If one of the reviewers tries to make changes to a read-only file, the changes can be saved only by giving the document a new name or saved to a new location.

• Save as read only
• Remove read only
• Restrict formatting and editing
• Unrestrict formatting and editing

Save as read only

1. Click the Microsoft Office Button , and then click Save or Save As if you have previously saved the document.
2. Click Tools.
3. Click General Options.
4. Click the Read-only recommended check box.
5. Click OK.
6. Save the document. You might need to save it as another file name if you have already named the document.

Remove read only

1. Click the Microsoft Office Button , and then click Save or Save As if you have previously saved the document.
2. Click Tools.
3. Click General Options.
4. Clear the Read-only recommended check box.
5. Click OK.
6. Save the document. You might need to save it as another file name if you have already named the document.

Restrict formatting and editing

The following are the steps for a second method for helping to stop content reviewers from accidentally modifying your document.

1. On the Review tab, in the Protect group, click Protect Document.
2. Under Restrict Reviewing Options, click Restrict Formatting and Editing.
3. In the Restrict Formatting and Editing task pane, under Formatting restrictions, Editing restrictions, and Start enforcement, make the selections that meet your formatting and editing needs.

Unrestrict formatting and editing

1. On the Review tab, in the Protect group, click Protect Document.
2. To clear the check mark, click Restrict Formatting and Editing under Restrict Reviewing Options.

Unrestricted, But all I want is if they click the RED X in the upper right corner to close the file and or program that it does not save any changes made to the document.  I want them to select the save and close button that I have created (macro) which is password protected.  This way if they do not know the password they can not close and save the changes.  So, if this happens they will click the RED X to close the document or the program, and in doing so I do not want any changes that they may have made to be shaved.  Thats it.

You can use the Workbook_BeofreSave to do this..  In the ThisWorkbook modules, put in this code:

Public bolManualSave        As Boolean

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    If Not bolManualSave Then
      Cancel = True
    End If

End Sub

Private Sub Workbook_Open()

    bolManualSave = False

End Sub


Then, modify your command button to set bolManualSave to true before you save, then reset it to false after you save:

Public Sub test()

    bolManualSave = True
.

.
..

    bolManualSave = False

End Sub

Open workbook in read only mode, use this macro

Sub testopen()
Const path As String = "C:\temp\test\abccompany#1.xls"
Workbooks.Open Filename:=path, ReadOnly:=True
End Sub