Excel workbook gets opened repeatedly after closing

Question

I have used the method Application.ontime() for scheduling some macros.After closing the workbook, it gets opened again and again. to overcome this problem, I set another event on workbook- BeforeClosed. Now it is showing runtime error 1004:Method 'OnTime' of 'Object'_Application failed.I am not getting why this happening even after reffering the help context from web. Below code is given.

Private Sub Workbook_Open()

starttime = Now + TimeValue("00:02:00")

Application.OnTime EarliestTime:=starttime, Procedure:="startapp", schedule:=True

rtime = TimeValue("14:30:00")

Application.OnTime EarliestTime:=rtime, Procedure:="sendreminder",  Schedule:=True   

Application.OnTime EarliestTime:=rtime, Procedure:="sendreminder_out",Schedule:=True  

Application.OnTime EarliestTime:=rtime, Procedure:="SendReminderFromProxy",Schedule:=True  


End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)

MsgBox "Dear" & " " & Environ("USERNAME") & ", " & "Please do not forget to save before closing."

starttime = Now + TimeValue("00:02:00")

Application.OnTime EarliestTime:=starttime, Procedure:="startapp", Schedule:=False

rtime = TimeValue("14:30:00")

Application.OnTime EarliestTime:=rtime, Procedure:="sendreminder", Schedule:=False       

Application.OnTime EarliestTime:=rtime, Procedure:="sendreminder_out", Schedule:=False

Application.OnTime EarliestTime:=rtime, Procedure:="SendReminderFromProxy", Schedule:=False

End Sub

[Related](https://stackoverflow.com/q/27005522/2662901) and [here](https://stackoverflow.com/q/1674467/2662901) — feetwet, Jan 07 '21 at 17:51

Tim Williams · Answer 1 · 2017-08-03T04:52:17.547

1

When cancelling an OnTime, you need to use the exact same argument values you used when creating it (except for the third argument).

That means you can't pass a different time when you cancel it.

Dim starttime '<<< store the time values for later use...
Dim rtime

Private Sub Workbook_Open()

    starttime = Now + TimeValue("00:02:00")

    Application.OnTime EarliestTime:=starttime, Procedure:="startapp", schedule:=True

    rtime = TimeValue("14:30:00")

    Application.OnTime EarliestTime:=rtime, Procedure:="sendreminder",  Schedule:=True   

    Application.OnTime EarliestTime:=rtime, Procedure:="sendreminder_out",Schedule:=True  

    Application.OnTime EarliestTime:=rtime, Procedure:="SendReminderFromProxy",Schedule:=True  


End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)

    MsgBox "Dear" & " " & Environ("USERNAME") & ", " & "Please do not forget to save before closing."

    'use the global variable here
    On Error Resume Next '<< prevent error if no schedule is set
                         '   or if already triggered
    Application.OnTime EarliestTime:=starttime, Procedure:="startapp", Schedule:=False
    Application.OnTime EarliestTime:=rtime, Procedure:="sendreminder", Schedule:=False       
    Application.OnTime EarliestTime:=rtime, Procedure:="sendreminder_out", Schedule:=False
    Application.OnTime EarliestTime:=rtime, Procedure:="SendReminderFromProxy", Schedule:=False
    On Error Goto 0

End Sub

edited Aug 03 '17 at 04:52

answered Aug 02 '17 at 17:37

Tim Williams

154,628
8
97
125

I tried using above code. Still it's showing error. – PRJ Aug 03 '17 at 03:40
from the workbook_BeforeClose() line- Application.OnTime EarliestTime:=starttime, Procedure:="startapp", Schedule:=False – PRJ Aug 03 '17 at 04:08
Now the error is : application-defined or object-defined error – PRJ Aug 03 '17 at 04:18
When it errors, go to the Immediate pane and type `? starttime` and press enter - what is printed? – Tim Williams Aug 03 '17 at 04:37
Nothing has been printed – PRJ Aug 03 '17 at 04:48
See my edit above: if there is no OnTime to cancel, or if it has already triggered, you will get an error when you try to cancel it, so you can ignore the error. – Tim Williams Aug 03 '17 at 04:53
although you used-On error resume next, it opens the workbook again. – PRJ Aug 03 '17 at 04:56
Seems like something is clearing your global variables: maybe an unhandled error in some other code? – Tim Williams Aug 03 '17 at 04:58
No. I think we have defined value for starttime and rtime in private function so another functions are not taking it. is it? – PRJ Aug 03 '17 at 05:16
Those variables are declared at the top of the ThisWorkbook module: that's where the event procedures that access them are located, so there's no problem there. – Tim Williams Aug 03 '17 at 05:23

Excel workbook gets opened repeatedly after closing

1 Answers1

Linked