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