Stopping running macro

[Johnny]

How I can stop following macro when I want?

Sub Timer()
Application.OnTime Now + TimeValue("00:00:10"), "Update"
End Sub

I would need a button which would stop the macro. Now the
macro keeps running for ages.

Thanks in advance!

 

[Frank Kabel]

Hi
assign the following macro to a button:
Sub StopIt()
Application.OnTime Now + TimeValue("00:00:10"), "Update",
schedule:=False
End Sub

 

[Tom Ogilvy]

See Chip Pearson's page for information on using Ontime and being able to
cancel it:

http://www.cpearson.com/excel/ontime.htm

 

[Chip Pearson]

Johnny,

To cancel an OnTime procedure, you need to provide the *exact*
time that the event is to run. Therefore, you should store the
run time in a public variable, and use that. For example, change
your existing code to

Public RunWhen As Double

Sub Timer()
RunWhen = Now + TimeValue("00:00:10")
Application.OnTime RunWhen, "Update"
End Sub

Then, you can cancel the procedure with

Sub CancelTimer()
Application.OnTime RunWhen, "Update", , False
End Sub

For more information, see www.cpearson.com/excel/ontime.htm .


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Tom Ogilvy]

This won't work since "Now" has changed since the event was schedule. You
have to store the next scheduled time in a static or global variable, then
schedule it using that variable, then use that stored value to cancel it.

Illustrated on Chip Pearson's page.
http://www.cpearson.com/excel/ontime.htm