Close without saving in Excel 2

[AcousticalConsultant]

Ok here's yet another question...
When the user closes the spreadsheet, I don't want excel to ask me if I want to save my changes. What I tried was this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Workbooks("myWorkbook.xls&quot.Close (False)

End Sub

but the problem with this is that I'm closing the workbook before Excel does. So once this module is finished, Excel can't find my workbook that it was asked to close! So I end up getting an illegal operation and Excel closes.

How can I get around this?

Thanks for the help!
Pascal

 

[dsi]

The problem is that the quit command has already started. You can insert this sub into a module (not the workbook or sheet modules). This should take care of your problem.

Sub Auto_Close()
    ActiveWorkbook.Close (False)
    Application.Quit
End Sub

 

[AcousticalConsultant]

dsi,

Yes, but that's my problem, is that I don't want to automatically close the workbook. Only once the user clicks close or X. But I don't want Excel to prompt the user to save changes!

Is this feasible? The only other way around it that I was able to come up with, is to make a custom button that serves to close the workbook, and we would have to click that button rather than File>Close... but that's kinda' crummy!

Thanks again,
Pascal

 

[vbMax]

Try using the Workbook_BeforeClose event...

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActiveWorkbook.Save
ThisWorkbook.Saved = True
End Sub

vbMax

 

[JustinEzequiel]

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Workbooks("myWorkbook.xls&quot.Saved = True

End Sub

This would 'tell' Excel that no changes have been made to the workbook since the last save.
Thus, Excel would not ask if you want to save it.

 

[AcousticalConsultant]

Thanks Justin!

I figured it out from vbMax's message, and it worked.

Thanks guys!