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

On Jul 11, 12:58 am, Dan <D...@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

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...@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
>
>

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" <(E-Mail Removed)> wrote in message
news:820DF93C-0742-4D99-8BF8-(E-Mail Removed)...
> 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

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" <(E-Mail Removed)> wrote in message
> news:820DF93C-0742-4D99-8BF8-(E-Mail Removed)...
> > 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
>