is there any way to add a timer object onto a VBA userform?

[jameshomer]

Can you create any kind of a timer object onto a VBA userform.
I wanted to place a ticking digital clock in
the corner of a userform. I've no problems making a digital clock but VBA seems to have no timer object and I really don't intend to use wait functions to stop the userform constantly.

Any help would be much appritiated (as would a spell checker )
This message was edited by jameshomer on 2002-07-19 04:46

 

[tusharm]

Use the OnTime method of the Application object.

Use the Userform_Activate event procedure initiate the timed procedure. It should subsequently reschedule itself. The Userform_Deactivate event procedure should stop the timed procedure.

 

[jameshomer]

Can you give me a little bit of sample code for this.
I have the code so it runs the macro first time around, but I don't see how you re-scedule the timer to repeat every second.

I have code that looks like this



private sub userform_activate()
application.ontime now,"procedure",now +timevalue("00:00:01")

this runs the macro the first time, but then stops. How do I get it to repeat and repeat and repeat and repeat and make me a cup of tea, and repeat and repeat

 

[Russell]

Sorry no Tea making facilities Excel gets a bit stewed.

Try this for repeating:

Private Sub userform_activate()
Dim NextTick

[procedure]

NextTick = Now + TimeValue("00:00:01")
Application.OnTime NextTick, "userform_activate"
End Sub


Russell

 

[tusharm]

On 2002-07-22 03:03, jameshomer wrote:
Can you give me a little bit of sample code for this.
I have the code so it runs the macro first time around, but I don't see how you re-scedule the timer to repeat every second.

I have code that looks like this



private sub userform_activate()
application.ontime now,"procedure",now +timevalue("00:00:01")

this runs the macro the first time, but then stops. How do I get it to repeat and repeat and repeat and repeat and make me a cup of tea, and repeat and repeat

I'm not sure what was on Russell's mind when he suggested scheduling the _Activate procedure repeatedly. You are on the right track having the _Activate procedure schedule another procedure. I would then have the other procedure schedule itself.

First, a syntactical issue. Check the syntax you are using for OnTime. It should be

Application.OnTime {scheduled time}, {procedure to execute}, _
    {schedule new item / cancel already scheduled event}

So, in whatever procedure you schedule through the _Activate sub, the last line should be:

Application.OnTime {new time}, {name of current procedure}

And your complete code should look something like this:

Dim SchedTime as Date
...
sub Userform1_Activate()
    ....
    SchedTime=Now()+{required duration}
    Application.OnTime SchedTime, "SchedProc"
    end sub
sub SchedProc()
    ... do your thing ...
    SchedTime=Now()+{required duration}
    Application.OnTime SchedTime, "SchedProc"
    end sub
sub Userform1_Deactivate()
    Application.OnTime SchedTime, "SchedProc", False
    ... whatever else is needed ...
    end sub

_________________
Regards,

Tushar
www.tushar-mehta.com
This message was edited by tusharm on 2002-07-22 06:15