Application.OnTime Stopping

Status
Not open for further replies.

clemscot

New Member
Joined
Dec 27, 2014
Messages
2
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
 
Status
Not open for further replies.

Forum statistics

Threads
1,081,617
Messages
5,360,044
Members
400,565
Latest member
Tommy O

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top