Announcement

Collapse
No announcement yet.

VBA: Stop/Cancel Application.Ontime

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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:
    Code:
     
    Private Sub Workbook_Open()
        Update
     
    End Sub
     
    Private Sub Workbook_BeforeClose(cancel As Boolean)
        StopRefresh
     
    End Sub
    Within a module:
    Code:
     
    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

  • #2


    Re: VBA: Stop/Cancel Application.Ontime

    I presume you have seen this thread:

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

    Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

    Comment

    Working...
    X