Auto update or refresh workbook in every 15 seconds

Hi All,

I have a workbook, which has some external web links to auto update the current time of different cities. The workbook automatically refreshes in every 1 minute, but I made below codes for auto refreshing the workbook in every 15 seconds.

Private Sub Workbook_Open()
' Written in ThisWorkbook
    Call RefreshTime
End Sub

Sub RefreshTime()
' Written in a module
    Application.ScreenUpdating = False
    ActiveWorkbook.RefreshAll
    Application.OnTime Now + TimeValue("00:00:15"), "RefreshTime"
    Range("B10").Value = "My Current Time of the System:"
    Range("C10").Value = Format(Now, "hh:mm:ss AM/PM")
    Beep
    Application.ScreenUpdating = True

End Sub

I see that it works fine, but I'm unable to stop it at any moment at the time of closing the workbook, and I am shocked when the workbook automatically opens just after the closing whether I save the data or not. :O

How can I do the auto updating activity of web links to a single worksheet instead of the whole workbook? I'm unable to work anywhere in this workbook due to it gets busy/ stuck while updating or refreshing.

How can I show/add a message in a cell: -

The workbook/ worksheet will be refreshed in 10 seconds
The workbook/ worksheet will be refreshed in 9 seconds
... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
The workbook/ worksheet will be refreshed in 1 second

Please find the attached workbook and try to help me for the right solution.
Thanks in advance!

MS Excel version: 2007

Hi SunOffice
Yeah, this is a bit scary isn't it

I am shocked when the workbook automatically opens just after the closing whether I save the data or not.

You need a Workbook BeforeClose Event to turn the timer off. Google on "stop timer on workbook close vba" and you'll find many references to this issue. If you can't get it resolved, let me know.

I googled and tried with some below codes, but nothing is working fine for me.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
' Written in ThisWorkbook
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Call StopTimer3     ' and I also tried to call the StopTimer1 & StopTimer2 but, tht was failed too.
    ActiveWorkbook.Save
    Application.EnableEvents = True
End Sub

' In a module:
Public RunWhen As Double
' ==============================================
Public SplashRunWhen As Double
Public Const cSplashRunIntervalSeconds = 60 'ONE minute = 60 Seconds
Public Const cSplashRunWhat = "SplashScreen_Open"

 ' In a module:
Sub StopTimer1()
    On Error Resume Next
    Application.OnTime EarliestTime:=RunWhen, Procedure:="CloseBook", Schedule:=False
End Sub
' ==============================================
Sub StopTimer2()
    On Error Resume Next
    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, Schedule:=False
    Application.OnTime EarliestTime:=SplashRunWhen, Procedure:=cSplashRunWhat, Schedule:=False
End Sub
' ==============================================
Sub StopTimer3()
'  http://www.cpearson.com/excel/OnTime.aspx

    On Error Resume Next
    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
        Schedule:=False
End Sub

I found StopTimer2 code from : http://www.excelforum.com/excel-prog...r-closing.html

Please see the above attached workbook and help me out....

Hi SunOffice
This code works for me:

In your ThisWorkbook Module

Option Explicit
Private Sub Workbook_Open()
    Call StartTimer
End Sub
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call StopTimer
End Sub

In a General Module

Option Explicit
Dim RunTime
 
Sub StartTimer()
    Application.ScreenUpdating = False
    ActiveWorkbook.RefreshAll
    RunTime = Now + TimeValue("00:00:15")
    Application.OnTime RunTime, "RefreshTime"
    Range("B10").Value = "My Current Time of the System:"
    Range("C10").Value = Format(Now, "hh:mm:ss AM/PM")
    Beep
    Application.ScreenUpdating = True
End Sub
 
Sub StopTimer()
'  http://www.cpearson.com/excel/OnTime.aspx
    On Error Resume Next
    Application.OnTime RunTime, "RefreshTime", Schedule:=False
    On Error GoTo 0
End Sub
 
Sub RefreshTime()
    ActiveWorkbook.Save
    StartTimer
End Sub

Do you have a macro called

"Refresh.Time"

The code references "RefreshTime"

It's working fine now!!

so you mean that here two macro codes will call each other for continuous refreshing the workbook in every 15 seconds of interval.

Thanks a lot Mr jaslake!

Hi SunOffice
The code I've provided is what I use. Yes

so you mean that here two macro codes will call each other for continuous refreshing the workbook in every 15 seconds of interval

as long as you've not closed the workbook, the Timer will run. When you close the workbook, the Timer is turned off.

If that satisfies your need, please mark your thread as "Solved".

To mark your thread solved do the following:
- Go to your first post on the thread
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solved
- Click Save