workbook before close for error checking

**clng** · 12-04-2007, 11:41 PM

Dear all,

I am writing some macro to test whether the summation in sheet5 and sheet7 summ up to zero (both also in their respective worksheet code) using worksheet deactivate.

this works wells when they try to switch to other worksheets, the macro will prompt them the error in summation and switch them back to that particular worksheet.

however, this error checking could not be extended when they immediately close the workbook without switching worksheets.

hence, I tried to include the same code in "Thisworkbook" code window but the workbook will continue to close even with the error prompt.
And i have 2 worksheets that needs to do the error check (i had both using cells instead of range so that I can do some offset and counting) and the "cell" usage is making me confused as to tell the macro which worksheet of those cells am i referring to.....

please help to provide some alternatives.

Thanks in advance.

Cheers,
CL

**jasoncw** · 12-05-2007, 12:44 AM

Hi, CL.

Your method will work, with just a little tweaking.

First, if you find the error, you not only need to activate the worksheet, you also need to set cancel = true. Setting cancel = true will cancel the workbook close. For example:

Please Login or Register  to view this content.

For the specific coding, if you post up your code for each of the sheet_deactivate event handler procedures, we can help you out with your workbook_beforeclose procedure.

HTH

Jason

**clng** · 12-05-2007, 01:13 AM

Hi Jason,

Thanks for the help, I guess by posting the attachment sample. It would be better.

Please take a look and advice.

Cheers,
CL

**jasoncw** · 12-05-2007, 11:58 AM

Hi, CL.

See the code in the ThisWorkbook module.

HTH

Jason

**clng** · 12-05-2007, 10:49 PM

Hi Jason,

Your code does work for WS5 but not WS7.

I had tried looking thru it but have no clue.

Please help again.

Cheers,
CL

**jasoncw** · 12-06-2007, 08:45 AM

What happened is that I simply copied your code from each of the worksheet modules into the ThisWorkbook module. Your code for sheet5 instructs to exit the procedure if it detects a 0 in column J. Since there is a 0, the procedure stopped, which, of course, will not allow it to check sheet7.

So all I did was tell it to go to the next worksheet if it detected a 0. Is that your intention?

Jason

**clng** · 12-06-2007, 01:18 PM

Hi Jason,

I need more help with tweaking.

I got a prompt to check "Priority Management", but it is non-existent. and both WS has no error.

I had try switching the logic sequence of "Goto NextWS" but still no use

Please help again.

Thank you very much.

Cheers,
CL

**jasoncw** · 12-06-2007, 02:37 PM

Sorry about that. I forgot a couple dots before the ranges...

**clng** · 12-07-2007, 12:05 AM

Hi Jason,

It works great. Thank you very much.

Didn't know that the "dot" is use to point to .select.
Hope to see your reply when I had other questions

Cheers,
CL