How to stop a running macro by user click event?

[Thom2718]

Warning! I'm a VBA newbie and this is my first post.

Inspired by a JWalk animated chart in Excel Charts, I have a running macro initiated by the user. The macro is animating a chart and the macro will terminate on its own if the user waits. However, I wish to let the user click a "reset" button that will do two things:
(1) exit out of the other macro
(2) reset the chart to a "default" state by setting a single cell value.

Option Explicit

Public SurplusIsRunning As Boolean

Sub RunAnimatedSurplus()
Dim wsCalc As Worksheet
Dim wsEquil As Worksheet
Set wsEquil = Worksheets("Equilibrium")
Set wsCalc = Worksheets("Calc")
If SurplusIsRunning Then
SurplusIsRunning = False
wsEquil.Range("FileInfo").Select
End
End If
SurplusIsRunning = True
wsCalc.Range("Q3").Value = 250
wsEquil.Range("FileInfo").Select
Do
DoEvents
wsCalc.Range("Q3") = wsCalc.Range("Q3") - 5
WaitATick 750 ' 1000 ms = 1 sec
Loop Until wsCalc.Range("P3").Value = _
wsCalc.Range("EqPrice").Value
wsEquil.Range("FileInfo").Select
SurplusIsRunning = False
StopIt:
SurplusIsRunning = False
wsEquil.Range("FileInfo").Select
End Sub
I have been getting VBA help from a Microsoft Excel MVP (hence the macro above and the omitted Function WaitATick(WaitTime As Integer)), but it's time I took some baby steps toward the deep end of the pool! Following is what I have, which doesn't work of course:

Sub ResetEquilibrium()
Dim wsCalc As Worksheet
Dim wsEquil As Worksheet
Set wsEquil = Worksheets("Equilibrium")
Set wsCalc = Worksheets("Calc")
If SurplusIsRunning Then
SurplusIsRunning = False
wsCalc.Range("Q3").Value = 100
wsEquil.Range("FileInfo").Select
End If
SurplusIsRunning = False
wsCalc.Range("Q3").Value = 100
wsEquil.Range("FileInfo").Select
End Sub

but SurplusIsRunning = False does not stop the first macro above. This is probably a silly question but I'm stuck and can't find the solution.

Thanks!

~Thom

[rory]

Try changing your Do Loop in the original sub to this:
Do
DoEvents
wsCalc.Range("Q3") = wsCalc.Range("Q3") - 5
WaitATick 750 ' 1000 ms = 1 sec
Loop Until wsCalc.Range("P3").Value = _
wsCalc.Range("EqPrice").Value Or SurplusIsRunning = False
If SurplusIsRunning = False Then
ResetEquilibrium
Exit Sub
End If


then have your ResetEquilibrium sub as:
Sub ResetEquilibrium()
Dim wsCalc As Worksheet
Dim wsEquil As Worksheet
Set wsEquil = Worksheets("Equilibrium")
Set wsCalc = Worksheets("Calc")
wsCalc.Range("Q3").Value = 100
wsEquil.Range("FileInfo").Select
End Sub


and have a separate Stop macro that just sets SurplusIsRunning = False.

HTH
Rory

[Thom2718]

Thanks, Rory! I'm almost to the perfect solution! The remaining issue is a "timing" thing, I'm sure. I have two buttons: "Start Animated Surplus" and "Reset Equilibrium." When the user hits the "Start Animated Surplus" button, I want them to have two choices while the animation macro is running:
Hit the same button a second time to stop the animation and "freeze" the linked chart wherever it happens to be.
Hit the "Reset Equilibrium" button to do two things: (a) stop the animation and (b) reset the price in the yellow shaded cells (K6:K8) to $1.00.What happened after implementing your solution and hitting the Reset Equilibrium button while the animation was running: the animation ran for one more step in the Do Loop after the price was reset and the RunAnimatedSurplus procedure executed wsCalc.Range("Q3") - 5 one more time before halting.

I have to get ResetEquilibrium to pause long enough so that the first macro is all done. So I added a line to ResetEquilibrium, WaitATick 1000 (which, of course, is a full second) and the first macro still steps through the Do Loop one more time.

???