VBA how to display real time clock in a userform?

Question

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!

Tomalak · Answer 1 · 2012-11-19T16:40:42.913

7

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.

edited Nov 19 '12 at 16:40

answered Nov 19 '12 at 08:43

Tomalak

332,285
67
532
628

It's not clear to me why this isn't working but it's not updating the label. (btw you're missing an underscore after `Procedure:="DisplayCurrentTime",`) – Brad Nov 19 '12 at 15:58
@Brad Thanks, the `_` 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"`). – Tomalak Nov 19 '12 at 16:46
Everything compiles. As far as I can tell it's not actually setting the timer. When I run it (`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. – Brad Nov 19 '12 at 17:20
@Brad See above (*Also, please try the fully qualified method name (i.e. `"MyProject.UserForm1.DisplayCurrentTime"`*) - Have you done this? There also were a few more questions. – Tomalak Nov 19 '12 at 18:04
Yes, sorry, I did try a fully qualified name. I tried making the sub public, too. Same results. – Brad Nov 19 '12 at 18:19
@Brad Does the fully qualified method name work when you try it manually - i.e. when the form is running and you execute it via the direct window? – Tomalak Nov 19 '12 at 18:41
I tried `Evaluate("UserForm1.DisplayCurrentTime")` while it was running but can you execute code in the immediate window while a form is not stopped at a breakpoint? Needless to say it did nothing. I'm generally confused here. This seems like the ideal answer. I'm not sure why it isn't happening! – Brad Nov 19 '12 at 19:00
@Brad Ah, I see what's wrong. The `OnTime()` method can only see public procedures (which includes normal modules, but excludes class modules like `UserForm`). The missing bit is here: http://stackoverflow.com/questions/1562913/timer-on-user-form-in-excel-vba, including a nicer implementation. – Tomalak Nov 19 '12 at 19:42
Looks like that'll do it! Surprisingly complicated. – Brad Nov 19 '12 at 20:31
@Brad There are third party Timer ActiveX components (and VB6 has a timer component of its own) but if you want to do it with VBA on-board means, that's as good as it will get, I'm afraid. – Tomalak Nov 19 '12 at 22:42
ya it looks pretty complex. i want my userform to call the displaytime() function which is in a normal module. that post looks like its the other way around. the original code you posted broke for me too, but it looks like its on the right track – thaweatherman Nov 20 '12 at 01:01

score 4 · Answer 2 · edited May 23 '17 at 11:48

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.

score 3 · Accepted Answer · edited Dec 23 '20 at 09:50

3

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.

edited Dec 23 '20 at 09:50

Tomer Shetah

8,413
7
27
35

answered Nov 29 '12 at 11:05

thaweatherman

1,467
4
20
32

I tried the above code; however it gives an error : "Cannot run the macro… the macro may not be available in this workbook". I Tried to make the Sub as Public, still no luck. I am calling the DisplayCurrentTime() in the Public Sub UserForm_Activate(). Please advice to resolve the issue. – Mufaddal Nov 26 '20 at 07:30
@Mufaddal, try to set the macro you want to run in a module and call/update the userform by the name. So label1.caption will be something like: Userform1.label1.caption = Time() – Joas van Doeselaar Dec 23 '20 at 09:23

VBA how to display real time clock in a userform?

3 Answers3