Stopping a timer
in [Programming]
|
Prev: Web pop up
Next: Move data between ListBoxes
From: OssieMac on 29 May 2010 21:02 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 -- Regards, OssieMac "ordnance1" wrote: > 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: OssieMac on 29 May 2010 21:14 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. -- Regards, OssieMac "ordnance1" wrote: > 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 > >> > . >
From: ordnance1 on 29 May 2010 21:43 Wow that was like a trip to the dentist. Thanks for all your help and patience "OssieMac" <OssieMac(a)discussions.microsoft.com> wrote in message news:9DA95688-D3EA-455E-B5F6-314BD5ABC022(a)microsoft.com... > 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 > > > > -- > Regards, > > OssieMac > > > "ordnance1" wrote: > >> 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 23:45 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
From: OssieMac on 30 May 2010 03:25
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 '@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ -- Regards, OssieMac |