Check before exiting SHEET1 if MsgBox is active or not and stop exit if MsgBox is active

[RIZVI]

Hello Friends!,

I got this workbook consisting of about 10 sheets (SHEET1 to SHEET10). SHEET1 is data entry. Other sheets fill in automatically based on data entered in SHEET1. SHEET1 got these MsgBoxes programmed to pop up just in case that a wrong entry is chosen or inserted something in error etc.

Even with a MsgBox being triggered, I can exit SHEET1 and click on SHEET2and this action does not stop me with a pop up saying that there is an error in SHEET1.

This is what I want. I need a macro to check all the MsgBoxes (before exiting the SHEET1) and deny permission to move if there is an error.

Will be pleased to hear and thank you for same.

Best wishes to all,
Rizvi Sameem

[Greg M]

Hi there,

Something like the following code entered into the VBA CodeModules of the worksheets you want to monitor might do what you need:

Please Login or Register  to view this content.

Hope this helps - please let me know how you get on.

Regards,

Greg M

[RIZVI]

Hi Greg,

Thank you very much. There is a "Run-time error '5':" appeearing with a message "Invalid procedure call or argument".

Regards
Rizvi

[Norie]

Rizvi

How are you currently displaying the message boxes?

[Greg M]

Hi again,

In the absence of a sample workbook I assumed that what you describe as "MsgBoxes" were Shapes named "shpMessageBox", and wrote the code accordingly.

If your "MsgBoxes" have some other form or name, the code will obviously fail.

When submitting a question here, you should ALWAYS include a sample workbook, otherwise proposed solutions can be based only on assumptions or suppositions.

Regards,

Greg M

[RIZVI]

Hello Norie and Greg,

Please accept my apology for not replyng you promptly. Here is the code and I got about 40 such codes running;

Please Login or Register  to view this content.

Regards
Rizvi

[Norie]

Where is this code located?

[RIZVI]

under; Private Sub Worksheet_Change(ByVal Target As Range)

[RIZVI]

This is the code;

Please Login or Register  to view this content.

[Greg M]

Hi again,

Thanks for that additional information. See if the following code does what you need - it should be entered in the VBA CodeModules of the worksheets you wish to monitor:

Please Login or Register  to view this content.

Hope this helps.

Regards,

Greg M

[RIZVI]

Hi Greg,

This is what I want, that one cannot leave SHEET1 unless the error input is corrected. Perfect! BUT;

As said,there are about 40 MsgBox codes entered;

Please Login or Register  to view this content.

So, for all 40 such codes I have to enter ".Activate" after the IF line. Actually took 5 codes, pasted under "Private Sub Worksheet_Deactivate()" tested by placing 5 such ".Active" codes and works perfectly. But the issue is each and every time I create a code and place it under my usual code "Private Sub Worksheet_Change(ByVal Target As Range)", I also have to create a duplicate and paste it under "Private Sub Worksheet_Deactivate()" because "Private Sub Worksheet_Change(ByVal Target As Range)" shows the MsgBox immediately where one can correct the mistake on the spot whilst "Private Sub Worksheet_Deactivate()" shows the MsgBox only when exiting the SHEET1.

What I acuallyy need is for the MsgBox to pop-up immediately if a wrong item is pressed or selected so that one can correct it immediately (Private Sub Worksheet_Change(ByVal Target As Range)) and I also need a separate code such as yours (Private Sub Worksheet_Deactivate()) to check in general if any such MsgBox in "Private Sub Worksheet_Change(ByVal Target As Range)" are triggered before exiting the SHEET1.

Thanks again Greg!

Best wishes
Rizvi

[Greg M]

Hi again,

As I said before, it is MUCH easier to suggest a solution when the entire scenario is visible from the start, instead of being drip-fed additional information when the suggested solution does not meet the (hitherto unstated!) requirements

See if the following code does what you need:

Please Login or Register  to view this content.

Regards,

Greg M

[RIZVI]

buddy,

It's perfect!, just what I needed. Tested on just 2 codes and works like a charm.
Thanks Greg, appreciated your effort in assisting.

Best wishes
Rizvi

[Greg M]

Hi again,

Many thanks for your feedback and also for the Reputation increase - much appreciated!

We got there in the end!

You're welcome - glad I was able to help.

Best regards,

Greg M