S
Stonewall Rubberbow
I'm wondering if someone can help here (running Excel 2003). I'm using VBA to
start a timer when the workbook opens. The timer calls a macro to save the
workbook every 10 minutes and restarts the timer. It works good, but the
issue I'm having is when I close the workbook, it's supposed to stop the
timer so that if Excel is left open it isn't still running the timer and
re-opening the workbook. I don't know why, but it is still opening the
workbook, even though I have a workbook-close event. And if you please, try
to not respond with a "why don't you just ____" response. I can't run any
add-ins, like AutoSave; our Excel is over a MetaFrame server and I don't have
authority, so everything must be VBA.
The VBA used is as follows:
In ThisWorkbook I have:
Private Sub Workbook_Open()
StartTimer
End Sub
Private Sub Workbook_Close()
StopTimer
End Sub
And in Module1 I have:
Public RunWhen As Double
Public Const cRunIntervalSeconds = 600
Public Const cRunWhat = "Save"
Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat,
Schedule:=True
End Sub
Sub Save()
ActiveWorkbook.Save
StartTimer
End Sub
Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat,
Schedule:=False
End Sub
start a timer when the workbook opens. The timer calls a macro to save the
workbook every 10 minutes and restarts the timer. It works good, but the
issue I'm having is when I close the workbook, it's supposed to stop the
timer so that if Excel is left open it isn't still running the timer and
re-opening the workbook. I don't know why, but it is still opening the
workbook, even though I have a workbook-close event. And if you please, try
to not respond with a "why don't you just ____" response. I can't run any
add-ins, like AutoSave; our Excel is over a MetaFrame server and I don't have
authority, so everything must be VBA.
The VBA used is as follows:
In ThisWorkbook I have:
Private Sub Workbook_Open()
StartTimer
End Sub
Private Sub Workbook_Close()
StopTimer
End Sub
And in Module1 I have:
Public RunWhen As Double
Public Const cRunIntervalSeconds = 600
Public Const cRunWhat = "Save"
Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat,
Schedule:=True
End Sub
Sub Save()
ActiveWorkbook.Save
StartTimer
End Sub
Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat,
Schedule:=False
End Sub