Stopping a timer

[ordnance1]

I have this code below that runs a timer on a 30 second cycle (1 of 3
timers). My problem is that my code to stop the timer does not work, so if
you close the workbook it restarts on its own. Can any one offer any help
with this? I am able to stop my other 2 timers (in an effort to minimize the
size of this post I did not include the code for the other 2 timers).

Adapted from code found on Chip Pearsons web site.


Public bSELCTIONCHANGE As Boolean
Public Cancel As Boolean
Public Const cRunIntervalSeconds = 30 'Time interval for pulling updated
data from Calendar (in seconds)
Public Const cRunWhat = "TheSub" ' the name of the procedure to run

Private Sub Workbook_Open()
Module2.TheSub
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
Schedule:=False
End Sub


Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
Schedule:=True
End Sub

Sub TheSub()
''''''''''''''''''''''''
' Your code here

Protection.UnProtectAllSheets

On Error GoTo NotKiosk


ThisWorkbook.UpdateLink Name:= _
"\\wtafx\public\Dispatch\Vacation\VacationCalendar 2010.xlsm",
Type:=xlExcelLinks
GoTo Continue

NotKiosk:
ThisWorkbook.UpdateLink Name:= _
"P:\Dispatch\Vacation\VacationCalendar 2010.xlsm",
Type:=xlExcelLinks

''''''''''''''''''''''''

Continue:

Protection.ProtectAllSheets

StartTimer ' Reschedule the procedure
End Sub

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

 

[OssieMac]

2 observations.

The timer is started when the workbook closes because it is in a
Workbook_BeforeClose event. How are you attempting to run the code to stop
the timer if the workbook is closed?

I can't see anywhere that you have declared the variable RunWhen. It needs
to be declared in the declarations section at the top of a STANDARD module as
follows otherwise the variable is not available to a different sub and/or
module.
Public RunWhen As Date

Note only need Dim RunWhen As Date if the variable is only used in different
subs in the same module but if in different modules then it needs to be
Public.

 

[ordnance1]

Here is a more cleaned up version with just the required code. When I close
the workbook (but not Excel) the workbook reopens after 30 seconds.


Module1

Public bSELCTIONCHANGE As Boolean
Public Cancel As Boolean
Public Const cRunIntervalSeconds = 30 'Time interval for pulling updated
data from Calendar (in seconds)
Public Const cRunWhat = "TheSub" ' the name of the procedure to run


---------------------------------------------------

Module2

Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
Schedule:=True
End Sub

Sub TheSub()
Msgbox"hello"
Continue:
StartTimer ' Reschedule the procedure
End Sub

Sub StopTimer()
On Error Resume Next
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
Schedule:=False
End Sub

------------------------------------------------

ThisWorkBook

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
Schedule:=False
End Sub

Private Sub Workbook_Open()
Module2.TheSub
End Sub

 

[ordnance1]

I have now added the line Public RunWhen As Date to module1 but workbook
still reopens after closing.

 

[ordnance1]

Is not the False at the end of that statement suppose to stop the timer? If
not then how can I stop it?

On Error Resume Next
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
Schedule:=False

 

[OssieMac]

Hi,

Now I can see a real problem. In StopTimer remove the line that resets the
value of RunWhen. To stop the timer the value of RunWhen must be the same
value that is used to start the timer. That is how Excel knows what timer to
stop.

StopTimer should be as follows.

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

Also in Module 1 where you declare variables insert the following line
because RunWhen must be available to all modules and all subs.

Public RunWhen As Date

As another suggestion you only need to call StopTimer from
Workbook_BeforeClose because you already have code written in module2.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call StopTimer
End Sub

 

[OssieMac]

Sorry. My error in not reading and interpreting correctly. See my last post
for answer to problem. Unfortunately you introduced another error in your
second post of the simplified code but it did explain what you were
attempting to do.

Basically in your first post your problem was not declaring RunWhen as
public so that its' value could be accessed in another module.

 

[ordnance1]

Wow that was like a trip to the dentist. Thanks for all your help and
patience

 

[ordnance1]

Well am excitement was short lived. After putting all changes in place I
started to test. I was able to stop all timers, I then restarted the
workbook and then closed it everything worked great. I then let the
WorkbookCloseTimer run its course and close the workbook, but then my
original problem returned and the workbook reopened. And the reopening was
caused by the UpdateTimer not stopping. I know the WorkbookBeforeClose
called the code to stop the timer (I placed a msgbox in the UpDateTimer stop
routine).

Not sure why the WorkbookBeforeClose would work with a manual close but not
a macro induced close. So here is the code in all its glory in the hopes
someone can explain why

Module1

Public bSELCTIONCHANGE As Boolean
Public Cancel As Boolean

'Code for Closing Workbook
Public Const NUM_MINUTES = 2
Public RunWhenClose As Double

' Code for the Data Update Timer

Public Const cRunIntervalSeconds = 30
Public Const cRunWhat = "TheSub"
Public RunWhen 'As Date

'Code for Splash Screen Timer
Public Const SPLASH_MINUTES = 1
Public RunWhenSplash As Double

Public Sub ShowMySplash()
ClosingSplashScreen.Show
End Sub

Public Sub SaveAndClose()

If ThisWorkbook.ReadOnly = False Then
ThisWorkbook.Close True
End If

If ThisWorkbook.ReadOnly = True Then
ThisWorkbook.Close False
End If

End Sub

=============================

Module2

Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
Schedule:=True
End Sub

Sub TheSub()
Protection.UnProtectAllSheets
My Code Here
StartTimer ' Reschedule the procedure
End Sub

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

============================

ThisWorkBook

Option Explicit
Private Sub Workbook_Open()

Module2.TheSub

'Codefor Workbook Close Timer
RunWhenClose = Now + TimeSerial(0, NUM_MINUTES, 0)
Application.OnTime RunWhenClose, "SaveAndClose", , True

'Code for Splash Screen Timer
RunWhenSplash = Now + TimeSerial(0, SPLASH_MINUTES, 0)
Application.OnTime RunWhenSplash, "ShowMySplash", , True

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Module4.StopSplashTimer
Module4.StopWorkBookCloseTimer
Call StopTimer

End Sub

 

[OssieMac]

I can't test all of your code because you have not shared the subs in
Module4. However, the following simple code works fine under test. Note I
always use Option Explicit so that any undeclared varibles are identified by
clicking Debug -> Compile.

Another observation is "Public Cancel As Boolean". I believe that Cancel is
a reserved word that is dimensioned in event subs and declaring it as a
Public constant could cause problems.

Also I see you have As Date commented out in Public RunWhen 'As Date
Trust me; it is a date. See the msgbox I have included in the workbook
close. This test might help you to determine if RunWhen is loosing its value
somewhere and therefore will not stop the timer.

'***********************************
'Module1 between asterisks
Option Explicit

Public Const cRunIntervalSeconds = 5 'I used 5 for testing
Public Const cRunWhat = "TheSub"
Public RunWhen As Date
'***********************************


'####################################
'Module2 between #'s

Option Explicit
Sub StartTimer()

MsgBox "TheSub" 'Used for testing

RunWhen = Now + _
TimeSerial(0, 0, cRunIntervalSeconds)

Application.OnTime _
EarliestTime:=RunWhen, _
Procedure:=cRunWhat, _
Schedule:=True
End Sub

Sub TheSub()

StartTimer
End Sub

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


'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
'ThisWorkbook between @'s

Option Explicit
Private Sub Workbook_Open()

Module2.TheSub 'Starts timer

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

MsgBox "RunWhen = " & RunWhen 'Test that Runwhen is not loosing its value
Call StopTimer

End Sub

'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

 

[ordnance1]

Good news

That did not work,

But I did solve it. While the Call StopTimer worked great if you manually
closed the workbook (but not when the WorkBookClose timer closed the
workbook) I found that I had to add Call StopTimer to Module1. I am sorry I
had not included all of Module1. I was trying to reduce the size of the post
and thought they missing code was not relevant.

So thank you for your help and I hope my omission did not cause you to much
extra work.



Module1

Public bSELCTIONCHANGE As Boolean
Public Cancel As Boolean

'Code for Closing Workbook
Public Const NUM_MINUTES = 2 'Time interval for closing the workbook(in
minutes)
Public RunWhenClose As Double

' Code for the Data Update Timer
Public Const cRunIntervalSeconds = 30 'Time interval for pulling updated
data from Calendar (in seconds)
Public Const cRunWhat = "TheSub" ' the name of the procedure to run
Public RunWhen 'As Date

'Code for Splash Screen Timer
Public Const SPLASH_MINUTES = 1 'Time interval for the Close Splash screen
(in minutes)
Public RunWhenSplash As Double

Public Sub ShowMySplash()
ClosingSplashScreen.Show
End Sub

Public Sub SaveAndClose()

If ThisWorkbook.ReadOnly = False Then
Call StopTimer
ThisWorkbook.Close True 'True causes the file to save when closed
End If

If ThisWorkbook.ReadOnly = True Then
Call StopTimer
ThisWorkbook.Close False 'False causes the file not to save when closed
End If

End Sub