Stop / reset all ontime events

[loloelpalomero]

Hi. I have used a few ontime events in a sheet that call a macro when time is reached as stated here.

I have noticed that, even if you close excel, the ontime vars are still stored and, if I re-open the file and change times in cells which are referenced from the ontime statement, some strange behavior occurs. Normally, I don't have to change cells containing times but, as time range is about an hour and a half, sometimes I change times to current for checking purposes and it seems to keep on counting or something...

What I would like to do is some kind of reset upon closing the sheet, or a button, macro or something for the ontime function.

Thanks.

 

[VoG]

You will need to do something like this. Right click the Excel logo just to the left of File on the menu bar, select View Code and paste in

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime earliesttime:=TimeValue([A1]), procedure:="colorbg_macro", schedule:=False
End Sub

You will need to qualify the sheet name in this statement

:=TimeValue([A1])

 

[loloelpalomero]

Hello VoG II. Thanks for answering but I have tested your code using this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime earliesttime:=TimeValue([Hoja1]), procedure:="colorbg01", schedule:=False
End Sub

"Hoja1" is name of the sheet. I have tried both names: The excel one (Sheet1=Hoja1) and the alias (the one wich appears in tabs below the sheet.

But error 438 is given. I am not sure how the exact error is in english as I use spanish version. But translated from spanish is something like "Object does not allow this property or method".

Any solution, please?

Thanks.

 

[VoG]

Try

Application.OnTime earliesttime:=TimeValue(Sheets("Hoja1").Range("A1").Value), procedure:="colorbg01", schedule:=False