Stopping a timer
in [Programming]
|
Prev: Web pop up
Next: Move data between ListBoxes
From: ordnance1 on 29 May 2010 15:39 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
From: OssieMac on 29 May 2010 19:00 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. -- Regards, OssieMac "ordnance1" wrote: > 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 >
From: ordnance1 on 29 May 2010 19:09 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
From: ordnance1 on 29 May 2010 19:58 I have now added the line Public RunWhen As Date to module1 but workbook still reopens after closing. "ordnance1" <ordnance1(a)comcast.net> wrote in message news:2E41527E-F2AF-405F-B923-47AD0C5D6A12(a)microsoft.com... > 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 > >
From: ordnance1 on 29 May 2010 20:01
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" <OssieMac(a)discussions.microsoft.com> wrote in message news:350E2E47-09C5-46D0-B9F8-F523CD3A3C73(a)microsoft.com... > 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. > > -- > Regards, > > OssieMac > > > "ordnance1" wrote: > >> 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 >> |