Macro to break loop/Sub on button press

Hi all

I am looking to exit a Sub (actual code is way too long to post here so just an example below) via a macro when the user presses a button.

The sort of Sub (without all the extra code) is similar to the below code - just runs from top to bottom and then jumps back up to the start again - an endless loop only stoppable by the user.

I had thought of just having a macro that changes a boolean variable (in this case Pressed) to true when run (via the button) but I cannot get the macro to execute (via the button) whilst the loop below is working.

I really need to user to be able to stop this Sub so any help is very much appreciated.

Redders

Please Login or Register  to view this content.

this part of the macro appears to be ok

before the statement
If Pressed = True then Goto Leave
introduce message box
msgbox pressed
check whether the message given is true or false or 1 or 0.

you can also use the statement
If Pressed = True then exit sub

I was wrong above. It is true that VBA is not multithreaded, but I just discovered a way to allow the thread to be interrupted and then return to it. I just happened to stumble on a post by user shg that mentions the built-in function DoEvents, which allows the user to press a button, have your code deal with it, then return to whatever was going on. Attached is a simple example. If you press the Test button, a form appears while a counter increments in A1. If you press Display Message, the process is interrupted while a msgbox pops up. If you press Halt, the whole thing halts.

Originally Posted by 6StringJazzer

I was wrong above. It is true that VBA is not multithreaded, but I just discovered a way to allow the thread to be interrupted and then return to it. I just happened to stumble on a post by user shg that mentions the built-in function DoEvents, which allows the user to press a button, have your code deal with it, then return to whatever was going on. Attached is a simple example. If you press the Test button, a form appears while a counter increments in A1. If you press Display Message, the process is interrupted while a msgbox pops up. If you press Halt, the whole thing halts.

Hi 6StringJazzer,

Can you provide the sample code in a Zip file. Work system restrictions prevent me from downloading XLSM files. This is something that I could really use. I have a VBA program that creates price lists and it could run for days and ineed a way of interupting it.

Thanks,

JimBobBowie

Thanks snb,

I am looking for a solution that allows the interuption to happen after the loop completes a cycle but before it starts a new cycle. I had a solution that worked but was given a new PC and now with W-7 and office 2010 it does not work.

JimBobBowie

Sorry, Didn't intend to hijack the thread I only thought that the sample file was a possible solution to my problem and I can't see it. I will wait until I get home tonight and I can view it there.

Thanks,

JimBobBowie

It sounds like the same problem with the same solution so here's the same file as a zip.

Thanks very much, I can make that solution work for what I am doing.

Cheers,

JimBobBowie