Thom2718
08-04-2007, 08:15 PM
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
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