Application.OnTime Error

[RobbieC]

Hi there, I have a macro which I'm using to toggle an auto-save. The Sub 'Auto_Save_Record' works fine and turns on the auto-save every minute, but my toggle only works to turn the Auto_Save_Record on.

When I toggle Auto_Save_Record to OFF, I get the Error 1004: Method 'OnTime' of object '_Application' failed

Sub toggleAutoSave()

Dim RunWhen As String
    RunWhen = Now + TimeValue("00:1:00")


    If Worksheets("Calculations").Range("P2") = False Then [I][COLOR=#ffa07a]'AutoSave is currently OFF[/COLOR][/I]


        Application.OnTime RunWhen, "Auto_Save_Record"
        Worksheets("Calculations").Range("P2") = "True" ' TURN AUTO-SAVE ON


    ElseIf Worksheets("Calculations").Range("P2") = True Then [COLOR=#ffa07a][I]'AutoSave is currently ON[/I][/COLOR]


    On Error GoTo ErrHandler
        Application.OnTime RunWhen, "Auto_Save_Record", schedule:=False
        Worksheets("Calculations").Range("P2") = "False" ' TURN AUTO-SAVE OFF


    End If
      
      Exit Sub


ErrHandler:
        MsgBox "Error " & Err.number & ": " & Err.Description


End Sub


Sub Auto_Save_Record()


    [I][COLOR=#a9a9a9]'code to save project - this works fine!!![/COLOR][/I]
    Dim ProjectDataDirectory As String
    ProjectDataDirectory = ThisWorkbook.Path & "\ProjectData\"
    ChDir ProjectDataDirectory
    Application.Run "DoneExCommand", 1, ProjectDataDirectory & Worksheets("Calculations").Range("O6") & ".dat"

    Dim RunWhen As String
    RunWhen = Now + TimeValue("00:1:00")



    Application.OnTime RunWhen, "Auto_Save_Record"
    Worksheets("Calculations").Range("P2") = True ' AUTO-SAVE ON


End Sub

I have lost track of the different variations that I have tried, but from what I've Googled and read, the above should work...

If you can help me out, I'd be very grateful...

Thanks

 

[RobbieC]

It must be something to do with the line:

Application.OnTime RunWhen, "Auto_Save_Record", schedule:=False

in the toggleAutoSave sub...

 

[shg]

When you unschedule a routine, you have to pass the same time that is currently scheduled to run.

Sub toggleAutoSave()
  Dim RunWhen       As [COLOR="#FF0000"]Date[/COLOR] ' << not string
  
  RunWhen = Now + TimeValue("00:1:00")
  
  If Worksheets("Calculations").Range("P2") = False Then  'AutoSave is currently OFF
    Application.OnTime RunWhen, "Auto_Save_Record"
[B]    Debug.Print "As of " & Now & " Scheduled for   " & RunWhen
[/B]    Worksheets("Calculations").Range("P2") = "True"  ' TURN AUTO-SAVE ON

  ElseIf Worksheets("Calculations").Range("P2") = True Then  'AutoSave is currently ON
    Application.OnTime RunWhen, "Auto_Save_Record", schedule:=False
[B]    Debug.Print "As of " & Now & " Unscheduled for " & RunWhen
[/B]    Worksheets("Calculations").Range("P2") = "False"  ' TURN AUTO-SAVE OFF
  End If
End Sub

Look in the Immediate window. If the schedule and unschedule times are not the same, or the schedule time is past and the event has already run, you will get a runtime error.

 

[RobbieC]

Thanks Shg, that did the trick!

 

[shg]

You're welcome.