Microsoft Excel - A Challenge, Is it possible?

Asked By Dan on 26-Aug-11 10:40 AM
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
Riley K replied to Dan on 26-Aug-11 10:49 AM
If unsaved workbooks are open when you use this method, Microsoft Excel displays a dialog box asking whether you want to save the changes. 

You can prevent this by saving all workbooks before using the Quit method or by setting the DisplayAlerts property to False. When this property is False, Microsoft Excel doesn’t display the dialog box when you quit with unsaved workbooks; it quits without saving them.

If you set the Saved property for a workbook to True without saving the workbook to the disk, Microsoft Excel will quit without asking you to save the workbook.

So one possibility:

Code:
Application.DisplayAlerts = False
Application.Quit


Regards
Dan replied to Riley K on 26-Aug-11 11:14 AM

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

Irfan Khan replied to Dan on 26-Aug-11 02:32 PM
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.

What do you want to do?

Save as read only

  1. Click the Microsoft Office Button Microsoft Office button image, 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 Microsoft Office button image, 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.
Dan replied to Irfan Khan on 26-Aug-11 03:29 PM
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.
wally eye replied to Dan on 26-Aug-11 06:34 PM
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

Anoop S replied to Dan on 27-Aug-11 06:13 AM
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