O
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
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