Auto Refresh every 3 minutes using a macro

[mleiman]

I have inserted a button to allow me to refresh my data (Various info from the web), however the data will only refresh when I click the button. Is there a way to make it auto refresh every 3 minutes?

Below is the data from visual basic:

Sub Refresh()
'
' Refresh Macro
'

'
ActiveWorkbook.RefreshAll
Range("B1").Select
End Sub



Thanks for the help

 

[Richard Schollar]

Hi

Check out the OnTime method of the Application object:

http://www.cpearson.com/excel/OnTime.aspx

 

[mleiman]

Below is what I have in my Macro: Still doesn't work......HELP!!!!


Sub Refresh()
'
' Refresh Macro
'


ActiveWorkbook.RefreshAll
Range("B1").Select
End Sub

Sub StartTimer()
RunWhen = Now + TimeSerial(0, 3, CRunIntervalSeconds)
Applicatoin.OnTime EarliestTime=RunWhen, Procedure=cRunWhat,
Schedule = True
End Sub



Thanks

 

[Richard Schollar]

Did you follow the steps on Chip's site? You need to define variables in the declarations section of your module (ie at the top before the macro code and this needs to be in standard module not the ThisWorkbook or a Worksheet module). Also, I noticed in your sub you had a typo:

Applicatoin.OnTime EarliestTime=RunWhen, Procedure="Refresh",
Schedule = True

and also that you should have defined RunWhen as:

RunWhen = Now + TimeSerial(0, 3, 0)

to call the macro a subsequent time, you need to call the StartTimer macro from within your other sub:

Sub Refresh()
'
' Refresh Macro
'


ActiveWorkbook.RefreshAll
Range("B1").Select
 
StartTimer
End Sub

which is why you need to have defined RunWhen as a public variable ie at the top of your module:

Public RunWhen As Variant

 

[mleiman]

is this what you mean? The Applicatoin.Ontime is some how incorrect b/c it shows up in red...

Sub Refresh()
'RunWhen = Now + TimeSerial(0, 3, 0)
Application.OnTime EarliestTime=RunWhen, Procedure=cRunWhat,
Schedule = True
' Refresh Macro
'


ActiveWorkbook.RefreshAll
Range("B1").Select

StartTimer
End Sub

Thanks again

 

[mleiman]

I have attempted to follow the directions on http://www.cpearson.com/excel/OnTime.aspx

However due to my elementry excel skills, i am not able to follow.

Below is the macro I have set up, not sure what/where to type necessary data to make macro reset every three minutes. All help appreciated.

Sub Refresh()
'
' Refresh Macro
'

'
ActiveWorkbook.RefreshAll
Range("B1").Select

End Sub