The ProblemSaw this on Alan's site today in a comment to a tip called "Mouse Click Event in VBA"
While my macro is running a repetitive task in a loop, I hit ESC and get out of it, wherever it is. What I would like is a way to get out only after finishing the complete task (a montecarlo simulation) so that it can be resumed where it was interrupted. Just detecting a keypress or mouse click somewhere that the macro could test at the end of the loop would be great.
I am just a beginner at excel VBA macros, and have been so for ten years.
I love that last comment. I've been doing this less that ten years and feel like I am learning like a newbie as every day passes.
First response (with edits, of course...)
I would try to implement a userform with a Pause/Resume toggle button. Clicking the button would change the state of the button (and the caption, of course).
When the code is running and hits the suspend point(s) it checks the state of the button, then continues on or suspends work.
You would also want to use the Click event handler for the button, checking the state change and resuming as appropriate.
Also, Alan has a hint or more on running macros in background mode. You might need to incorporate that information.
Second Response (with edits, of course...)
- Running Macros in the Background
- Aborting a Macro and Retaining Control
- Pausing Macros for User Input
- Working while a Macro is Running
- The Start button goes dark
- The Pause button, which is a toggle rather than a command button, is enabled along with the check box for stopping the process altogether
- Processing stops, for now
- The Pause button turns into a "Resume" button
- The Stop check box is enabled
- The Resume button reverts to a Pause
- If the Stop check box is unchecked the process continues
- If the Stop check box is on (checked) then
- Processing ends
- The Start button is enabled
- The Stop check box is now unchecked (Can't stop something that isn't running!)
- The Pause button and Stop check box are both disabled.
The Code (Version 1)
' This is the control module
Dim cel As Range
Set cel = ThisWorkbook.Sheets(1).Range("A1")
Do ' Loop runs until the Check Box for stopping the run is clicked on _
See comments in UserForm object module
cel.Value = AddAndDivide(cel)
If ufmStartPauseResumeStop01.tgbPauseResume Then
Loop Until Not ufmStartPauseResumeStop01.tgbPauseResume
Loop Until ufmStartPauseResumeStop01.cbxStop
ufmStartPauseResumeStop01.cbtStart.Enabled = True
ufmStartPauseResumeStop01.tgbPauseResume.Enabled = False
ufmStartPauseResumeStop01.cbxStop = False
ufmStartPauseResumeStop01.cbxStop.Enabled = False