Using Worksheet Deactivate event to run macro and stop deactivation if required

[Bonnister]

Hello folks

Haven't been on here for a little while, hope everyone is well and someone might be able to help me?

I've written the following code to basically calculate if there are any blanks cells in a specific range and advise the user that they need to revisit these cells if they click to access any other sheet in the workbook:

Please Login or Register  to view this content.

This code works absolutely fine no problem.

The problem is that if the result of the calculation is greater than 0 the message box pops up AFTER the sheet has been deactivated and the user has successfully activated another sheet. What I want it to do is to calculate the formula and if the result is greater than 0 then it stops the deactivation of the worksheet, so that when the user reads the warning message and clicks the OK button they are still in the same sheet.

Is there a way of doing this at all?

Thanks everyone

Kenny

[stnkynts]

Please Login or Register  to view this content.

[Bonnister]

Perfect stnkynts, thanks very much, I'd hoped it would be simple and I had actually tried putting a sheet activate line in my code, but I was putting it after the end if so even though it did work it kept throwing up an error message (something to do with it couldn't cope with the activate instruction or something like that...)

Anyway, works a treat, thanks once again

Kenny