Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 2 of 2

Thread: VBA: Stop/Cancel Application.Ontime

  1. #1
    Join Date
    31st July 2010
    Posts
    3

    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:
    VB:
     
    Private Sub Workbook_Open() 
        Update 
         
    End Sub 
     
    Private Sub Workbook_BeforeClose(cancel As Boolean) 
        StopRefresh 
         
    End Sub 
    
    
    Within a module:
    VB:
     
    Public dtime As Double 
     
    Sub Update() 
        Windows("Testrss.xls").Activate 
        ActiveWorkbook.XmlMaps("rss_Map").DataBinding.Refresh 
        Sheet2.Cells.EntireRow.AutoFit 
         
        RefreshXML 
         
    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,

    Josh

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    8th December 2006
    Location
    New Zealand
    Posts
    3,923

    Re: VBA: Stop/Cancel Application.Ontime

    I presume you have seen this thread:

    http://www.ozgrid.com/forum/showthread.php?t=144836

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 HELP
    Replies: 5
    Last Post: September 29th, 2012, 08:04
  2. Stop/Cancel Application OnTime
    By RSW in forum EXCEL HELP
    Replies: 7
    Last Post: July 30th, 2010, 06:04
  3. Stop / Cancel An OnTime Macro
    By jhayes in forum EXCEL HELP
    Replies: 8
    Last Post: January 12th, 2008, 08:38
  4. Stop OnTime Method Macro
    By Elmura in forum EXCEL HELP
    Replies: 4
    Last Post: August 5th, 2007, 12:06
  5. Cancel Application.OnTime
    By gollem in forum EXCEL HELP
    Replies: 1
    Last Post: April 14th, 2006, 20:06

Bookmarks

Posting Permissions

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