How to Cancel a Save?

  • Thread starter Thread starter Rodg2000
  • Start date Start date
R

Rodg2000

I have a macro that runs as an Auto_Close when a person doubleclicks to
close a spreadsheet, or chooses the close on the File menu. As part of the
macro, I do some checking and if I determine that there has been a
particular error made, I want to pop up a MsgBox alerting the person to the
error, and then force them to fix it prior to doing the save (ie, abort the
Save in progress and just resume on the spreadsheet).

I have everything figured out except... once I am into the Auto_Close macro,
and I find the error and report it, I can't figure out how to prevent the
Standard Close message "Save.. Yes, No, or Cancel" from appearing right
after the MsgBox and the subsequent Save being done. I don't want them to
be able to respond Yes, or for that matter have to respond at all. How do I
put the brakes on the Save that Excel is trying to do?

TIA Rodg2000
 
Rodg,

Assuming that you have XL97 or later, you should use the
Workbook_BeforeClose event, which has a Cancel argument. If you find a
condition that causes you to want to Cancel, set Cancel to True.

This event code goes into the ThisWorkbook code module.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top