Create custom command to stop code at any point

[sczerniak]

Hi All, I am trying to get a piece of macro that will allow me to create a command which ends the macro while running. I know that the standard command is ctrl+break, but I don't want my users to end the macros prematurely so I disabled that command like this:

Please Login or Register  to view this content.

Now I would like to have the option to stop the code while I am running it by creating something else which causes the script to stop. My idea would be something like this but I'm not quite sure how to write it properly:

Please Login or Register  to view this content.

[abousetta]

Hi,

There's a much simpler way...

Please Login or Register  to view this content.

And assign a button that when triggered changes the UserStopped to True.

abousetta

[sczerniak]

I'm not quite sure I fully understand what you mean. The macro I am using changes between sheets pretty quickly so I'm not sure I would even be able to click a button if I needed to exit the macro.

[abousetta]

Use DoEvents... when you have that in place (e.g. during loops) the macro always stops to check if there are any other commands before continuing.

For example:

Please Login or Register  to view this content.

As the code is running, click on Esc. Normally if there is a loop in place, nothing will happen. Because of DoEvents, you will get a msgbox saying that the code was interrupted. Same concept but put an if statement before the Doevents.

[sczerniak]

I figured out a way to do it within Application.EnableCancelKey. Basically, when the user tries to exit the macro a box comes up asking them to enter the escape code. If they enter the escape code right, then they can exit.

Please Login or Register  to view this content.