CODEWITHSUNDEEP
excelvba
Arshit patel
Arshit patel

Reputation: 133

Run time error 1004 method "ontime" of object failed

I have following VBA code to refresh all sheets on specific interval

Public interval As Double

Sub macro_timer()

interval = Now + TimeValue("00:50:00")

End Sub

Sub my_macro()

 End Sub

 Sub stop_macro()
 Application.OnTime earliesttime:=interval, procedure:="my_macro",schedule:=False

  End Sub

When I try to run stop_macro it throws run time error.

Upvotes: 0

Views: 1914

Answers (1)

Robert Mearns
Robert Mearns

Reputation: 11986

A bit of VBA weirdness, if the previous scheduled procedure is not there, it will fail. Change the last parameter to True.

Sub my_macro()

    MsgBox "Hello World"

End Sub

Sub stop_macro()

     interval = Now + TimeValue("00:01:00")

     Application.OnTime interval, "my_macro",, True

End Sub

Upvotes: 4

Related Questions