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.

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, procedu=cRunWhat, _
schedule:=True
End Sub

Sub The_Sub()
Calculate
StartTimer
End Sub

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


and then run the StartTimer macro.
--
Gary''s Student - gsnu200789


"Dan" wrote:

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.

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

"Gary''s Student" wrote:

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, procedu=cRunWhat, _
schedule:=True
End Sub

Sub The_Sub()
Calculate
StartTimer
End Sub

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


and then run the StartTimer macro.
--
Gary''s Student - gsnu200789


"Dan" wrote:

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.

YOU answered my dilema perfectly, Thank you.

"Gary''s Student" wrote:

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, procedu=cRunWhat, _
schedule:=True
End Sub

Sub The_Sub()
Calculate
StartTimer
End Sub

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


and then run the StartTimer macro.
--
Gary''s Student - gsnu200789


"Dan" wrote:

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.

Glad to help. Have a good weekend!!
--
Gary''s Student - gsnu200789


"Dan" wrote:

YOU answered my dilema perfectly, Thank you.

"Gary''s Student" wrote:

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, procedu=cRunWhat, _
schedule:=True
End Sub

Sub The_Sub()
Calculate
StartTimer
End Sub

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


and then run the StartTimer macro.
--
Gary''s Student - gsnu200789


"Dan" wrote:

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.