none
Excel workbook gets opened repeatedly RRS feed

  • 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. Please give me solution.

    Dim starttime,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." 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

    Thursday, August 3, 2017 4:12 AM

All replies

  • In the first place, you should use the existing value starttime in Workbook_BeforeClose, not a new value, because you want to stop the already scheduled action.

    In the second place, if the user starts to close the workbook, then cancels the prompt to save it, the scheduled actions will have been canceled. When the user starts to close the workbook again, the Workbook_BeforeClose event will run again and cause an error since there are no scheduled actions to cancel anymore. So we have to prevent that.

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        MsgBox "Dear" & " " & Environ("USERNAME") & ", " & "Please do not forget to save before closing."
        On Error Resume Next
        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
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, August 3, 2017 5:37 AM
  • Hi Pooja,

    Has your issue been resolved? If it has, I would suggest you mark the helpful reply as answer which is the way to close a thread here.

    If not, please feel free to keep following up.

    Regards,

    Tony


    Help each other

    Wednesday, August 9, 2017 7:14 AM
  • Hi Hans,

    I am getting the same error {method 'ontime' of object '_application' failed}

    I am passing the same time to Application.OnTime For Schedule:=False, which I have passed while calling the timer to start the procedure.

    Your help will be appreciable.

    Thanks & Regards,

    Vikram Mankar.

    mankarvikram@gmail.com

    Thursday, June 6, 2019 2:34 PM
  • The easiest way to prevent the error is to insert a line

    On Error Resume Next

    above the line with Schedule:=False


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, June 6, 2019 3:30 PM