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.