So I need to display a real-time clock on my form but I am not sure how. I do know that the code:
TimeValue(Now)
will give me the current time, but I am not sure how to continually display this on my form.
I have an idea which is to put this code inside of a loop as follows:
Dim bool As Boolean
bool = True
Do While bool = True
Label1.Caption = TimeValue(Now)
Loop
However I am not sure where to put this code. Any suggestions would be greatly appreciated!
Excel has the OnTime method that allows you to schedule the execution of any procedure.
Just use it to schedule a one-second rhythm.
Sub DisplayCurrentTime()
Dim nextSecond As Date
nextSecond = DateAdd("s", 1, Now())
Label1.Caption = Now()
Application.OnTime _
Procedure:="DisplayCurrentTime", _
EarliestTime:=nextSecond, _
LatestTime:=nextSecond
End Sub
Start the loop by calling DisplayCurrentTime() once, for example in the initialize method of your form.
Procedure:="DisplayCurrentTime",)
_ is fixed. -- Does it compile? Have you set breakpoints/Debug.Print calls to see what it's doing? Can you try without the LatestTime parameter? Is the DisplayCurrentTime() public in your UserForm module? Also, please try the fully qualified method name (i.e. "MyProject.UserForm1.DisplayCurrentTime").DisplayCurrentTime called in the Initialize event) no error but the label is only set once. If I step through it I get the error The Macro may not be available in this workbook of all macros may be disabled. The file is saved as an xlsm."MyProject.UserForm1.DisplayCurrentTime") - Have you done this? There also were a few more questions.The problem with your atempt would be, that you are effectively creating an infinite loop.
Your Excel would use up quite some CPU-Time and might even block user-input, because it would excecute your while-statements, as fast as it can.
Look at this example http://www.andypope.info/vba/clock.htm or at the solutions in this post How do I show a running clock in Excel?
They should help you.
At least you should include a DoEvents statement in your loop.
I solved the issue others were having with the given code in the chosen answer. I removed the latesttime line and i changed Label1.caption = Now() to use Time() instead.
Sub DisplayCurrentTime()
Dim nextSecond As Date
nextSecond = DateAdd("s", 1, Now())
Label1.Caption = Time()
Application.OnTime _
Procedure:="DisplayCurrentTime", _
EarliestTime:=nextSecond
End Sub
I then called this in my userform_initialize function. Label1.caption was changed to the appropriate label on my userform.
I put a Timer event with an interval of 500ms on the form - it seems to work OK for me. It simply updated an unbound text control.
Private Sub Form_Timer()
txtLocalTime.Value = Time()
End Sub
I suppose if you're going to use the Form Timer event to do something else, then it could be an issue, but, I believe in keeping things simple :-)
