Deactivate Event only on if statment

**clemsoncooz** · 03-30-2012, 04:26 PM

I would like to write a if statment within the worksheet_deactivate event that only allows the user to deactivate the worksheet when something is true.

Please Login or Register  to view this content.

If the above is not the case, then do not allow the user to deactivate the worksheet.

EDIT: and also display a msgbox as to why they are unable to deactivate the worksheet.

**Paul** · 03-30-2012, 04:30 PM

Hi Clemson,

I don't believe you can (or should want to) do this. The Deactivate event (workbook or worksheet) don't accept the argument "Cancel as Boolean" like the Workbook_BeforeSave and _BeforeClose events do.

I'd recommend you prevent them from saving if those conditions are met, but not changing sheets. In that case, use the Workbook_BeforeSave event and add the line

Please Login or Register  to view this content.

to that procedure, e.g.

Please Login or Register  to view this content.

You can add other code, such as a message box telling the user why the save was canceled.

**clemsoncooz** · 03-30-2012, 04:38 PM

Thanks for the heads up Paul. I appreciate your advice.

But this document is a tool for preparing proposals and contracts and this information is critical. I do not want the user to move from this page (worksheet) if that criteria isn't meet because if they do and continue to use the tool (without the above being true) and work with it - then that can be bad).

So you're saying that cant be done?

**Paul** · 03-30-2012, 04:44 PM

You can't use the Cancel = True method, but you could use something like:

Please Login or Register  to view this content.

**clemsoncooz** · 03-30-2012, 04:51 PM

Wonderful - Thank you. I do believe this works just what I asked for.

Now - can I throw a wringle in there?

I want to change the if statment to be just like it is, but ONLY compare it to Range("E2") when there is a value in it. when its blank or zero, I only want B4 to be compared to B2 and I need more help with that code.

**clemsoncooz** · 03-30-2012, 05:04 PM

Maybe I got it:

Please Login or Register  to view this content.

Thanks for all the help!!!

**Paul** · 03-30-2012, 05:10 PM

You're welcome! Glad to see you sorted out the last bit on your own. If you wouldn't mind, please mark the thread as Solved by going to the first post and clicking 'Thread Tools' in the bar above it, then selecting 'Mark this thread as solved.'

Thanks!

EDIT: I see you've already done that too! Wonderful!