MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Macro to run every 15 minutes while file is open


Posted by Dwight on July 23, 2001 11:06 AM

I have a file which contains a macro to refresh stock price via web query. Any way to make the macro run automatically every 15 minutes so long as the file is open?
Thanks


Posted by Barrie Davidson on July 23, 2001 12:07 PM

Dwight, try this

Sub Yoursub()
Your macro......
Timer
End Sub


Sub Timer()
Application.OnTime Now + TimeValue("00:00:05"), "Yoursub"
End Sub

Regards,
Barrie

Posted by Dwight on July 23, 2001 2:15 PM

Barry: Maybe I didn't follow you exactly .......

Barrie:

Maybe I didn't understand your instructions:

I created a macro:
Sub StockQuoteAutoRefresh()
'
' StockQuoteAutoRefresh Macro
' Macro recorded 7/23/01 by Dwight Hamilton
(Lots of lines of intervening code)
End With
Timer
End Sub

Then created another macro:
Sub Timer()
Application.OnTimeNow+TimeValue("00:15.00),StockQuoteAutoRefresh
' Timer Macro
' Macro recorded 7/23/01 by Dwight Hamilton
'

End Sub

Doesn't seem to do anything. What am I doing wrong?
Thanks,
Dwight

Posted by Barrie Davidson on July 23, 2001 2:52 PM

Re: Barry: Maybe I didn't follow you exactly .......

StockQuoteAutoRefresh Macro Macro recorded 7/23/01 by Dwight Hamilton

Dwight, put your macro name in quotations. So change:
Application.OnTimeNow+TimeValue("00:15.00),StockQuoteAutoRefresh
to:
Application.OnTimeNow+TimeValue("00:15.00),"StockQuoteAutoRefresh"