Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

Results 1 to 2 of 2

Thread: VBA: Stop/Cancel Application.Ontime

  1. #1
    Join Date
    31st July 2010

    VBA: Stop/Cancel Application.Ontime

    Hi All,

    I'm a novice when it comes to VBA and am usually able to find solutions by searching the forums, however I just can't seem to get this one right.

    I've read most of the sources I could find in regards to starting a looping timer on workbook open and then stopping it upon workbook close, and have gotten it to start and continue looping just fine... the problem is I can't get it to stop. If I close the workbook and continue working on other spreadsheets it will open again shortly after.

    Essentially I'm trying to get a macro to run every 5 minutes, starting when the workbook is initially opened and ending when the workbook closes.

    Below is the code I'm using:

    Update = the procedure I want to run every 5 minutes
    RefreshXML = the Ontime loop
    StopRefresh = cancel the Ontime loop

    Within the ThisWorkbook object:
    Private Sub Workbook_Open() 
    End Sub 
    Private Sub Workbook_BeforeClose(cancel As Boolean) 
    End Sub 
    Within a module:
    Public dtime As Double 
    Sub Update() 
    End Sub 
    Sub RefreshXML() 
        dtime = Now + TimeValue("00:05:00") 
        Application.OnTime dtime, "Update" 
    End Sub 
    Sub StopRefresh() 
        On Error Resume Next 
        Application.OnTime dtime, "Update", Schedule:=False 
    End Sub 
    If anyone could let me know what I'm doing wrong i'd be grateful.

    Please let me know if I need to provide any additional details.

    Thanks so much,


    Excel Video Tutorials / Excel Dashboards Reports

  2. #2
    Join Date
    8th December 2006
    New Zealand

    Re: VBA: Stop/Cancel Application.Ontime

    I presume you have seen this thread:


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Stop, Cancel OnTime Event Macro
    By JHughes in forum Excel General
    Replies: 5
    Last Post: September 29th, 2012, 08:04
  2. Stop/Cancel Application OnTime
    By RSW in forum Excel General
    Replies: 7
    Last Post: July 30th, 2010, 06:04
  3. Stop / Cancel An OnTime Macro
    By jhayes in forum Excel General
    Replies: 8
    Last Post: January 12th, 2008, 09:38
  4. Stop OnTime Method Macro
    By Elmura in forum Excel General
    Replies: 4
    Last Post: August 5th, 2007, 12:06
  5. Cancel Application.OnTime
    By gollem in forum Excel General
    Replies: 1
    Last Post: April 14th, 2006, 20:06


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts