Adding a clock to an Excel User Form

Posted by Paul Harrison on February 08, 2002 4:52 AM

How can i Place a real time clock on to a Userform. The Time function returns the current system time, could I do it by running that function every minute? If so how would I do it and would it slow the program down?

Posted by Ivan F Moala on February 08, 2002 11:29 AM

Here is one way to do it...there are others but
they involve a little more. I'm actually looking
@ another way to do it but need to work out a few
more things.......but this should work OK.
you may notice a flicker now and then but it still
operates ok.

In a sepaerate module place this code.

Option Explicit
Dim T

Sub StopTimer()
On Error Resume Next
Application.OnTime T, Procedure:="Update", Schedule:=False
End Sub

Sub StartTimer()
T = Now + TimeValue("00:00:01")
Application.OnTime T, "Update"
End Sub

Sub Update()
Userform1.TextBox1.Value = Format(Now, "hh:mm:ss")
Call StartTimer
End Sub

In your UF you should have a Textbox & commandbtn.
Note:in this example the userform name = Userform1
the Textbox is called Textbox1

Change as required

'Userform Code
Private Sub CommandButton1_Click()
Application.Run "StopTimer"
Unload Me
End Sub

Private Sub UserForm_Activate()
Application.Run "StartTimer"
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
StopTimer
End Sub

HTH

ivan