Workbook-Close StopTimer event
 

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, Procedu=cRunWhat,
Schedule:=True
End Sub

Sub Save()
ActiveWorkbook.Save
StartTimer
End Sub

Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=RunWhen, Procedu=cRunWhat,
Schedule:=False
End Sub

Workbook-Close StopTimer event
 

There is no Workbook_Close event.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
StopTimer
End Sub


Gord Dibben MS Excel MVP

On Wed, 9 Jan 2008 07:11:00 -0800, Stonewall Rubberbow
wrote:

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, Procedu=cRunWhat,
Schedule:=True
End Sub

Sub Save()
ActiveWorkbook.Save
StartTimer
End Sub

Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=RunWhen, Procedu=cRunWhat,
Schedule:=False
End Sub

Workbook-Close StopTimer event
 

Thanks for the reply, that worked well!

"Gord Dibben" wrote:

There is no Workbook_Close event.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
StopTimer
End Sub


Gord Dibben MS Excel MVP

On Wed, 9 Jan 2008 07:11:00 -0800, Stonewall Rubberbow
wrote:

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, Procedu=cRunWhat,
Schedule:=True
End Sub

Sub Save()
ActiveWorkbook.Save
StartTimer
End Sub

Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=RunWhen, Procedu=cRunWhat,
Schedule:=False
End Sub