John asks how one macro can be used to stop another macro. One way to do it is with a variable that both procedures can see. In this implementation, I have two buttons from the Forms toolbar that start and stop a counter in A1.
The code is all in one standard module and looks like this:
Sub StartIt()
bStopped = False
Sheet1.Shapes(“Button 1”).Locked = True
Do While Not bStopped
DoEvents
With Sheet1.Range(“A1”)
.Value = .Value + 1
End With
Loop
Sheet1.Shapes(“Button 1”).Locked = False
End Sub
Sub StopIt()
bStopped = True
End Sub
StartIt runs until the variable is made true by StopIt. The DoEvents in the loop releases control of the computer to any other processes in the queue. In this case, running StopIt will queue it up but won’t run unless you let it. It’s kind of like letting people in to your lane – DoEvents is that jerk in front of you that’s letting everyone in.
I set the Locked property of the Start button, but I’m not sure if I know what I’m doing. Without those lines pressing Start repeatedly crashed Excel without fail. With those lines, no problems.
The key is to make your variable available to both the start and stop procedures. Since both procedures in this example are in a standard module, my variable can be private to that module. It’s the smallest scope that both procedures can see. If my procedures were in a userform, a variable that was private to the userform’s class module would do the trick. If the procedures are in different modules, you’ll need to declare the variable as Public in a standard module.