Tags: applicationontime, cancelling, error, excel, following, microsoft, msdn, orchange, process, repeatively, run, software, timing

Cancelling Application.OnTime

On Microsoft » Microsoft Excel

6,160 words with 4 Comments; publish: Sun, 01 Jun 2008 14:34:00 GMT; (30678.13, « »)

Hi,

I need to run a process repeatively, but I need to be able to stop it too or

change the timing , but keep getting the following error when trying to stop

the process " Run-Time error '1004': Method 'ONTIME' of Object 'Application'

Failed"

Any idea why, or how I should do it instead.

Many thanks

Sub RunMacro()

Application.OnTime Now + TimeValue("00:00:05"), "OnTimeMacro"

End Sub

Sub OntimeMacro()

MsgBox "hello"

RunMacro

End Sub

Sub byebye()

Application.OnTime EarliestTime:=TimeValue("00:00:05"),

Procedure:="my_Procedure", Schedule:=False ' this is where I keep getting the

error?

MsgBox "Bye Bye"

End Sub

All Comments

Leave a comment...

  • 4 Comments
    • On Jul 11, 12:58 am, Dan <D....excel.todaysummary.com.discussions.microsoft.com> wrote:

      > Hi,

      > I need to run a process repeatively, but I need to be able to stop it too or

      > change the timing , but keep getting the following error when trying to stop

      > the process " Run-Time error '1004': Method 'ONTIME' of Object 'Application'

      > Failed"

      > Any idea why, or how I should do it instead.

      > Many thanks

      > Sub RunMacro()

      > Application.OnTime Now + TimeValue("00:00:05"), "OnTimeMacro"

      > End Sub

      > Sub OntimeMacro()

      > MsgBox "hello"

      > RunMacro

      > End Sub

      > Sub byebye()

      > Application.OnTime EarliestTime:=TimeValue("00:00:05"),

      > Procedure:="my_Procedure", Schedule:=False ' this is where I keep getting the

      > error?

      > MsgBox "Bye Bye"

      > End Sub

      Hello Dan,

      This should help. It is from the VBA help file...

      OnTime Method Example

      This example runs my_Procedure 15 seconds from now.

      Application.OnTime Now + TimeValue("00:00:15"), "my_Procedure"

      This example runs my_Procedure at 5 P.M.

      Application.OnTime TimeValue("17:00:00"), "my_Procedure"

      This example cancels the OnTime setting from the previous example.

      Application.OnTime EarliestTime:=TimeValue("17:00:00"), _

      Procedure:="my_Procedure", Schedule:=False

      Sincerely,

      Leith Ross

      #1; Sun, 01 Jun 2008 14:35:00 GMT
    • Hi,

      Thanks, but that is exactly my question - I have access to this page too,

      and I copied it but as per my question this is not working and this was what

      I was asking.

      "Leith Ross" wrote:

      > On Jul 11, 12:58 am, Dan <D....excel.todaysummary.com.discussions.microsoft.com> wrote:

      > Hello Dan,

      > This should help. It is from the VBA help file...

      > OnTime Method Example

      > This example runs my_Procedure 15 seconds from now.

      > Application.OnTime Now + TimeValue("00:00:15"), "my_Procedure"

      > This example runs my_Procedure at 5 P.M.

      > Application.OnTime TimeValue("17:00:00"), "my_Procedure"

      > This example cancels the OnTime setting from the previous example.

      > Application.OnTime EarliestTime:=TimeValue("17:00:00"), _

      > Procedure:="my_Procedure", Schedule:=False

      > Sincerely,

      > Leith Ross

      >

      #2; Sun, 01 Jun 2008 14:36:00 GMT
    • You need to pass the EXACT time of the timer back to OnTime in order to

      cancel the timer. Therefore, you should create a public variable outside of

      any procedure and store the time in that variable.

      Public RunWhen As Double

      Sub RunMacro()

      RunWhen = Now + TimeValue("00:00:05")

      Application.RunWhen, "OnTimeMacro"

      End Sub

      Sub byebye()

      Application.OnTime EarliestTime:=RunWhen, _

      Procedure:="my_Procedure", Schedule:=False

      MsgBox "Bye Bye"

      End Sub

      See http://www.cpearson.com/excel/ontime.aspx for full details.

      Cordially,

      Chip Pearson

      Microsoft MVP - Excel

      Pearson Software Consulting

      www.cpearson.com

      (email on the web site)

      "Dan" <Dan.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      news:820DF93C-0742-4D99-8BF8-30225ADF73F0.excel.todaysummary.com.microsoft.com...

      > Hi,

      > I need to run a process repeatively, but I need to be able to stop it too

      > or

      > change the timing , but keep getting the following error when trying to

      > stop

      > the process " Run-Time error '1004': Method 'ONTIME' of Object

      > 'Application'

      > Failed"

      > Any idea why, or how I should do it instead.

      > Many thanks

      >

      > Sub RunMacro()

      > Application.OnTime Now + TimeValue("00:00:05"), "OnTimeMacro"

      > End Sub

      > Sub OntimeMacro()

      > MsgBox "hello"

      > RunMacro

      > End Sub

      > Sub byebye()

      > Application.OnTime EarliestTime:=TimeValue("00:00:05"),

      > Procedure:="my_Procedure", Schedule:=False ' this is where I keep getting

      > the

      > error?

      > MsgBox "Bye Bye"

      > End Sub

      #3; Sun, 01 Jun 2008 14:37:00 GMT
    • Thank you very much

      "Chip Pearson" wrote:

      > You need to pass the EXACT time of the timer back to OnTime in order to

      > cancel the timer. Therefore, you should create a public variable outside of

      > any procedure and store the time in that variable.

      > Public RunWhen As Double

      > Sub RunMacro()

      > RunWhen = Now + TimeValue("00:00:05")

      > Application.RunWhen, "OnTimeMacro"

      > End Sub

      > Sub byebye()

      > Application.OnTime EarliestTime:=RunWhen, _

      > Procedure:="my_Procedure", Schedule:=False

      > MsgBox "Bye Bye"

      > End Sub

      > See http://www.cpearson.com/excel/ontime.aspx for full details.

      >

      > --

      > Cordially,

      > Chip Pearson

      > Microsoft MVP - Excel

      > Pearson Software Consulting

      > www.cpearson.com

      > (email on the web site)

      >

      > "Dan" <Dan.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      > news:820DF93C-0742-4D99-8BF8-30225ADF73F0.excel.todaysummary.com.microsoft.com...

      >

      #4; Sun, 01 Jun 2008 14:38:00 GMT