How do i get an automatic refresh of a worksheet in excel

[Dan]

I have a 2007 Excel worksheet that uses time that is updated to get
information for other cells. Rather than continuously hit the F9 key. I
would like to set an automatic refresh every 20 seconds or so.

 

[Gary''s Student]

Insert the following in a standard module:

Public RunWhen As Double
Public Const cRunIntervalSeconds = 20
Public Const cRunWhat = "The_Sub"
Public Const a = "a"

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

Sub The_Sub()
Calculate
StartTimer
End Sub

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


and then run the StartTimer macro.

 

[Dan]

Thank you so much....Works perfectly....

Insert the following in a standard module:

Public RunWhen As Double
Public Const cRunIntervalSeconds = 20
Public Const cRunWhat = "The_Sub"
Public Const a = "a"

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

Sub The_Sub()
Calculate
StartTimer
End Sub

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


and then run the StartTimer macro.

 

[Dan]

YOU answered my dilema perfectly, Thank you.

Insert the following in a standard module:

Public RunWhen As Double
Public Const cRunIntervalSeconds = 20
Public Const cRunWhat = "The_Sub"
Public Const a = "a"

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

Sub The_Sub()
Calculate
StartTimer
End Sub

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


and then run the StartTimer macro.

 

[Gary''s Student]

Glad to help. Have a good weekend!!