Application.OnTime Stopping

[clemscot]

So I created the below schedule within the ThisWorkbook object. The schedule worked effectively (when excel wouldn't get closed due to system restart or some other reason - or execute multiple time due to state <> false). However after 5 days the schedule just stopped firing. I isolated events and times for launch and ensured that this sub was the active code and, nothing.

Tried the same file on multiple boxes to no avail. I understand Application.OnTime is fickle and its state must be set to false when closing (this is for a whole other topic as I have been frustrated as can be with this) or it will continue to launch multiple executions of the same command - hopefully MS will come up with a better solution to scheduling needs.

There is nothing wrong with any of the subroutines that are called as I can executed them manually with no issues. My only resolution was to create an entirely new file. I imported all objects (sheets and modules) and simply cut/paste the below schedule into the new ThisWorkbook object - boom, it worked fine - ugh!

Any one else out there encountered this and have you discovered any cause for it to just stop working?



Private Sub Workbook_Open()

'This schedule creates the FULL 24 Hour file
Application.OnTime TimeValue("00:00:00"), "Rodeodata00"
Application.OnTime TimeValue("01:00:00"), "RodeoData01"
Application.OnTime TimeValue("02:00:00"), "RodeoData02"
Application.OnTime TimeValue("03:00:00"), "RodeoData03"
Application.OnTime TimeValue("04:00:00"), "RodeoData04"
Application.OnTime TimeValue("05:00:00"), "RodeoData05"
Application.OnTime TimeValue("06:00:00"), "RodeoData06"
Application.OnTime TimeValue("07:00:00"), "RodeoData07"
Application.OnTime TimeValue("08:00:00"), "RodeoData08"
Application.OnTime TimeValue("09:00:00"), "RodeoData09"
Application.OnTime TimeValue("10:00:00"), "RodeoData10"
Application.OnTime TimeValue("11:00:00"), "RodeoData11"
Application.OnTime TimeValue("12:00:00"), "RodeoData12"
Application.OnTime TimeValue("13:00:00"), "RodeoData13"
Application.OnTime TimeValue("14:00:00"), "RodeoData14"
Application.OnTime TimeValue("15:00:00"), "RodeoData15"
Application.OnTime TimeValue("16:00:00"), "RodeoData16"
Application.OnTime TimeValue("17:00:00"), "RodeoData17"
Application.OnTime TimeValue("18:00:00"), "RodeoData18"
Application.OnTime TimeValue("19:00:00"), "RodeoData19"
Application.OnTime TimeValue("20:00:00"), "RodeoData20"
Application.OnTime TimeValue("21:00:00"), "RodeoData21"
Application.OnTime TimeValue("22:00:00"), "RodeoData22"
Application.OnTime TimeValue("23:00:00"), "RodeoData23"



'This schedule creates the current shift file - DAYS
Application.OnTime TimeValue("06:32:00"), "ShiftRodeoData1"
Application.OnTime TimeValue("07:00:00"), "ShiftRodeoData2"
Application.OnTime TimeValue("08:00:00"), "ShiftRodeoData3"
Application.OnTime TimeValue("09:00:00"), "ShiftRodeoData4"
Application.OnTime TimeValue("10:05:00"), "ShiftRodeoData5"
Application.OnTime TimeValue("11:03:00"), "ShiftRodeoData6"
Application.OnTime TimeValue("12:00:00"), "ShiftRodeoData7"
Application.OnTime TimeValue("13:00:00"), "ShiftRodeoData8"
Application.OnTime TimeValue("14:00:00"), "ShiftRodeoData9"
Application.OnTime TimeValue("15:00:00"), "ShiftRodeoData10"
Application.OnTime TimeValue("16:00:00"), "ShiftRodeoData11"
Application.OnTime TimeValue("17:00:00"), "ShiftRodeoData12"
Application.OnTime TimeValue("18:00:00"), "ShiftRodeoData13"

'This schedule creates the current shift file - NIGHTS
Application.OnTime TimeValue("18:30:00"), "ShiftRodeoData1"
Application.OnTime TimeValue("19:00:00"), "ShiftRodeoData2"
Application.OnTime TimeValue("20:00:00"), "ShiftRodeoData3"
Application.OnTime TimeValue("21:00:00"), "ShiftRodeoData4"
Application.OnTime TimeValue("22:00:00"), "ShiftRodeoData5"
Application.OnTime TimeValue("23:00:00"), "ShiftRodeoData6"
Application.OnTime TimeValue("00:00:00"), "ShiftRodeoData7"
Application.OnTime TimeValue("01:00:00"), "ShiftRodeoData8"
Application.OnTime TimeValue("02:00:00"), "ShiftRodeoData9"
Application.OnTime TimeValue("03:00:00"), "ShiftRodeoData10"
Application.OnTime TimeValue("04:00:00"), "ShiftRodeoData11"
Application.OnTime TimeValue("05:00:00"), "ShiftRodeoData12"
Application.OnTime TimeValue("06:00:00"), "ShiftRodeoData13"

End Sub

 

[Andrew Poulsom]

Duplicate:

http://www.mrexcel.com/forum/excel-questions/825973-application-ontime-stopping.html