Consulting

Results 1 to 5 of 5

Thread: Solved: Application.Ontime Assistance

  1. #1

    Solved: Application.Ontime Assistance

    I've created a workbook that uses the Application.Ontime procedure. However, I'm having some problems getting it to work as intended and wondered if there was a simple solution.

    So far, this is how it's working.

    Workbook opens, calls sub:
    [vba]

    Private Sub Workbook_Open()

    Call timetest

    End Sub
    [/vba]

    Sub then calls seperate macros to run at intervals of 2 seconds and then repeats when the final stage is run be calling the macro again at stage 5:
    [vba]Sub timetest()
    Application.OnTime Now + TimeValue("00:00:02"), "stage1"
    Range("A1") = "1"
    Application.OnTime Now + TimeValue("00:00:04"), "stage2"
    Range("A1") = "2"
    Application.OnTime Now + TimeValue("00:00:06"), "stage3"
    Range("A1") = "3"
    Application.OnTime Now + TimeValue("00:00:08"), "stage4"
    Range("A1") = "4"
    Application.OnTime Now + TimeValue("00:00:10"), "stage5"
    Range("A1") = "5"
    End Sub[/vba]

    [vba]Sub stage5()
    Range("C2") = "this?"
    Call timetest
    End Sub[/vba]

    Now, I had experimented with getting this all to run in a single sub, but it kept falling over, so I broke it into sections like this and it ran smoothly.

    However, , it keeps running even if you change workbooks...as the aim of this is to populate cells to simulate a ticker tape, that means it's writing all over any other workbook the user switches to...

    And, if you close the workbook, it simply re-opens itself and keeps on running!!!

    I'd like to say I'm asking for help, but in this case, I really need someone to tell me what the smeg I've done wrong!!

    Any insight would be appreciated, I've previously built my own timers and thought I would use this function instead here...teething issues!!

    Thanks

    Phel

  2. #2
    VBAX Tutor lynnnow's Avatar
    Joined
    Jan 2005
    Location
    Mumbai, Maharashtra, India
    Posts
    299
    Location
    One thing you could do is set the necessary workbook to a value and when you write to that workbook, use the variable you have used to specifically write it to.

  3. #3
    Yeah, sorry I should have mentioned.

    The workbook\sheet was named in the staged subs however, navigating away from that workbook was impossible as it would simply drag you back to run the code again.

    What I think I need to do is to lock the code down to run only in that specific workbook and sheet, but only when it's active.

    I did try an "If workbooks("XYZ").active" condition, but I couldn't make it work.

    Perhaps a thisworksheet code instead of modular?

  4. #4
    VBAX Tutor lynnnow's Avatar
    Joined
    Jan 2005
    Location
    Mumbai, Maharashtra, India
    Posts
    299
    Location
    Yeah, a ThisWorkbook Activate event would be a better option.

  5. #5
    This isn't really resolved, but I'm closing the thread as I've found an alternative way of achieving the same result.

    Thank you for your help though.

Posting Permissions

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