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.

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

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

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.

On 22 Oct, 12:34, Chip Pearson wrote:
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

Seehttp://www.cpearson.com/excel/OnTime.aspxfor much more
information about working with OnTime and Windows timers.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
* * Excel Product Group
Pearson Software Consulting, LLCwww.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.- Hide quoted text -

- Show quoted text -

brilliant, thanks guys!