Hi Bero,
Matt already gave the solution. You just need to work with it a bit to make it fit your situation.
Immediately stop? Maybe not. But, all you need in your routine is something that checks to see if you have pressed the stop button.
Let me re-write Matt's code and see if I can make it more clear how this works.
I put this code in a general code module:
And this code on the sheet that has a "start" and a "stop" button ...
You click on button 1 (start) and the infinite loop proceeds. You click on button 2 and it changes the value of x to make the loop stop.
Instead of x an integer, maybe you would want to use a boolean. But, the point it, make it a global variable (that is, one defined as Public in the declarations section of a general code module). Then, at key points in your routine be sure to put in the DoEvents statement (to allow Excel to recognize the Stop button was pressed). And, check to see if the value of the "stop" variable has changed.
I have similar long-running operations in Excel and Access that use this technique. It works.
Bookmarks