If you need to cancel a pending OnTime event, you must provide the
*exact* time that was specified when that event was scheduled. This
means that you should declare a module-scope variable, store the time
in that variable, and use that variable to cancel the OnTime. For
example,

Dim RunWhen As Double

Sub StartTimer()
RunWhen = Now + TimeSerial(0,1,0) ' one minute
Application.OnTime RunWhen,"ProcName",,True
End Sub

Sub StopTimer()
Application.OnTime RunWhen,"ProcName",,False
End Sub

See http://www.cpearson.com/excel/OnTime.aspx for much more
information about working with OnTime and Windows timers.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Wed, 22 Oct 2008 03:31:54 -0700 (PDT), macroapa
wrote:

Hi,

I have some code that I want to run every 30 seconds and have the
following:

Application.OnTime Now + TimeValue("00:00:30"), "dowerefresh"

However, what I want is a button to be able to switch it on and off.

I have a public boolean called autorefresh

I was going to do a loop while autorefersh = true, but i'm concerned
that this will just start multiple onTime events?

What is the best way to achieve this?

Thanks for any help.