PDA

View Full Version : Solved: Application.Ontime Assistance



Phelony
08-03-2011, 02:15 AM
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:


Private Sub Workbook_Open()

Call timetest

End Sub


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:
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

Sub stage5()
Range("C2") = "this?"
Call timetest
End Sub

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, :banghead: , 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

lynnnow
08-03-2011, 02:39 AM
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.

Phelony
08-03-2011, 02:45 AM
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?

lynnnow
08-03-2011, 03:00 AM
Yeah, a ThisWorkbook Activate event would be a better option.

Phelony
08-04-2011, 08:49 AM
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.