"OnTime" works - but only once per 'session&#

  • Hi, and thanks for the help before on this topic.

    Using "OnTime" in my personal.xls worksheet to run a different macro at a specific time works great - but it only works one time - the first time that specified time occurs after opening Excel.

    If I leave Excel open overnight during the next night, "OnTime" doesn't run - apparently because it already ran 24 hours previously and Excel wasn't 're-opened' in the interim.

    Is there a way to make OnTime run every night without closing Excel and re-opening it at least once every 24 hour period? As it works now, the update doesn't occur over the weekend (not in the office to close and re-open Excel) or on weeknights where I forget to close and re-open it.



  • Ok, I like to think I'm not completely dense, but I have no idea how to do what you just suggested. ;)

    Are you saying it's just a matter of adding more "OnTime" lines to the macro - i.e. it'll run one, then 24 hours run the next line, etc. - or something more specific that resets it to run 'from the top' again?


  • Ok, thaniks!

    Much of that was over my head, but here's what I think I saw...

    In my PERSONAL.XLS "ThisWorkbook" section, my current code is this:
    Private Sub Workbook_Open()
    Application.OnTime TimeValue("05:05:00"), "BDSUpdate"
    End Sub

    If I read that site correctly, it suggests I do something like the following instead:

    Private RunWhen As Double
    Private Const cRunIntervalHours = 24
    Private Const cRunWhat = "BDSUpdate"

    Sub StartTimer()

    RunWhen = TimeValue("05:05:00") + TimeSerial(0, 0, cRunIntervalHours)
    Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
    End Sub

    ... and then add the "StartTimer" line to the bottom of the actual "BDSUpdate" macro...

    I think?

    *scratches head*

  • All you really need is to put at the bootom of your BDSUpdate macro the one line
    Application.OnTime TimeValue("05:05:00"), "BDSUpdate"
    That will have it schedule itself to run when 05:05 rolls around, which will be the next day.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!