Auto refresh macro?

[cpg84]

Does anyone know if it is possible to have a macro that behaves the same as if you held down the F9 button (refresh)?

I have a clock formula (Weeks, Days, Hours, Minutes, Seconds in separate cells) and I want to press a button to get the screen to constantly refresh so you can see the time tick down.

Also, if I manage to get this, will this affect other, unrelated spreadsheets I have open at the same time?

Thanks

[dave3009]

You can turn off workbook calculation then use the ontime method to calculate a specific sheet. This will mean that the other sheets will need to be calculated manually or by way of an event.

Sub turnoff()
'Turn Off calculation and start call ontime routine
Application.Calculation = xlCalculationManual
Call DoTimer
End Sub

Sub DoTimer()
'Call the sheet calculation macro every one second
Application.OnTime Now + TimeValue("00:00:01"), "Rep"

End Sub

Sub Rep()
' calculate the sheet and repeat the ontime process
Sheet1.Calculate
Call DoTimer
End Sub

[cpg84]

Cool, thanks.

I will give it a try when I get home this arvo. Hopefully it will work.