MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Stop workbook_BeforeClose From Running

Posted by JAF on January 17, 2000 5:23 AM

I have a number of departmental data input sheets which have a Workbook_BeforeClose macro that displays a warning message if the workbook contains any blank cells in the data input area and gives the user the option of either closing the file or keeping it open to correct the "errors".

There are 36 different departments all contributing data to their own specific file and I have a macro that goes into each file at month end and clears out the previous months input, saves and closes the file, but since I added the Workbook_BeforeClose macro to the input sheets, I now get this "warning " message displayed when I run the "clear out" macro.

I still want the warning message to display if the file is opened by the user, but not if it's opened by running the "clear out" macro.

Setting the cells to a zero value is not an option as there could well be instances where a value of zero is valid one month but not the next and I need to ensure that the file has been updated.

I've tried using the Application.DisplayAlerts = False command, but this hasn't worked.

Any suggestions greatly appreciated - JAF

Posted by on January 17, 2000 1:32 PM


It wouldn't be very elegant, but it would be simple.

Have the clear out macro set a particular cell to x. Then have the before close procedure check that cell. If the cell contents are x, then don't display message, and set cell value back to y. If the cell contents are y, then display the message.

There's no doubt a better solution, but it will work.