Over-riding Message Boxes Using Visual Basic

[Nigel Smith]

I am writing a macro that opens a specified file and
temporarily places data in it. I want to be able to
automatically close the file without saving the changes.

At present, when the Macro reaches the
activeworkbook.close function, Excel effectively "jumps
out" of the Macro until the user responds Yes/No/Cancel to
the "Save Changes" message box, at which point the macro
continues.

Is there anyway of automating this response?

 

[Don Guillett]

From vba help for "saved"
Example
This example displays a message if the active workbook contains unsaved
changes.

If Not ActiveWorkbook.Saved Then
MsgBox "This workbook contains unsaved changes."
End IfThis example closes the workbook that contains the example code and
discards any changes to the workbook by setting the Saved property to True.

ThisWorkbook.Saved = True
ThisWorkbook.Close-- Don GuillettSalesAid (e-mail address removed)"Nigel

 

[Stephen Bullen]

Hi Nigel,

I am writing a macro that opens a specified file and
temporarily places data in it. I want to be able to
automatically close the file without saving the changes.

In this specific case, Workbook.Close takes an optional argument to
determine whether to save the workbook. If you say False, it won't be
saved:

oWorkbook.Close savechanges:=False

In other circumstances, you can set Application.DisplayAlerts to false
to turn off these messages, remembering to turn them back on
afterwards!

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk