update of info

Posted by Brian on January 28, 2002 10:48 AM

I have automatic calculate on, but I have to alter the something on the sheet before any new calcs occur. Is there a way so the screen refreshes every 5 minutes without me doing anything?

Posted by Juan Pablo G. on January 28, 2002 12:30 PM

This code has been shamesly copied from CPearson's site. You may attach one button to StartTimer and another one to StopTimer, or put them in Workbook_Open and Workbook_BeforeClose, depends on what you want.

Public RunWhen As Double
Public Const cRunIntervalSeconds = 300 ' five minutes
Public Const cRunWhat = "MyCalculate"

Sub StartTimer()

RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
schedule:=True
End Sub

Sub MyCalculate()

ActiveSheet.Calculate
StartTimer

End Sub

Sub StopTimer()
On Error Resume Next
Application.OnTime earliesttime:=RunWhen, _
procedure:=cRunWhat, schedule:=False
End Sub

Juan Pablo G.