Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
before close event with condition | Excel Discussion (Misc queries) | |||
Workbook event code won't work on laptop | Excel Worksheet Functions | |||
Event: open workbook | Excel Discussion (Misc queries) | |||
Event when workbook is saved or closed | Excel Discussion (Misc queries) | |||
workbook/sheet event macro | Excel Discussion (Misc queries) |