Excel - Ontime function

Asked By macroapa on 22-Oct-08 07:53 AM

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

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.

Joe replied on 22-Oct-08 07:01 AM
I would use a checkbox instead of a button.  Fill in the checkbox Linked Cell
property to be a cell on the worksheet.  Then have your refresh code test the
value of the linked cell.

You will also need a CheckBox1_Click function to start the On time function
the first time

Private Sub CheckBox1_Click()
if Range("A1") = true then

call dowerefresh
End Sub

sub dowerefresh
if Range("A1") = true then
Application.OnTime Now + TimeValue("00:00:30"), "dowerefresh"
end if
end MyOntime

You could also Make the checkbox Linked cell a named range DoRefresh and
then make the test in the above functions

if Range("DoRefresh") = true then
Chip Pearson replied on 22-Oct-08 07:34 AM
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

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.

Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
(email on web site)

On Wed, 22 Oct 2008 03:31:54 -0700 (PDT), macroapa
macroapa replied on 25-Oct-08 11:43 AM
brilliant, thanks guys!