a clock in a userform

[Nick Vittum]

Once again, this is primarily for educational purposes. I have no immediate need for this to work; I just want to understand the principles involved

• 1) Although the clock appears to be working when you open the workbook, there's a hidden error somewhere. You can tell this because if you go to VBA editor, the code is frozen, can't be edited until you reset it. Once you reset, of course, the clock goes away.
  
  2) If I do not have

  Code: Select all

      ClockTextBox = Format(Now, "mm-dd-yy, h:mm:ss AM/PM")

  in the form's initialize event, the clock opens blank, and doesn't show anything until "prompted" by calling the "Clock" sub.
  
  3) If I attempt to call the Clock sub directly from UserForm_Initialize, either the whole thing freezes, or I get an error message saying the macro cannot be used, depending on exactly how I go about it. However, I can summon it elsewhere and it works—except for the hidden error noted in (1), above.
  
  4) My guess is the hidden error is related to the "Loop" command—but how does one fix that? I've tried "While" and "Until" conditions, but can't find a way to make them work.
  
  5) I tried adding a MsgBox that might tell me what error was occurring—but got nothing

Note: the MouseDown event would serve no purpose when the clock works properly. It's only my way of calling <Clock> to see that the sub itself works properly.

[HansV]

Updating the text box in the after Update event of the text box causes the event to call itself over and over again, which will eventually freeze Excel. It's better to use Application.OnTime. See the attached version.

time.xlsm

[Nick Vittum]

Updating the text box in the after Update event of the text box causes the event to call itself over and over again, which will eventually freeze Excel. It's better to use Application.OnTime.

I suspected the endless loop was the problem. But I guess I'll have to study your code side by side with mine to understand why yours doesn't do that.

Once again, my thanks!

[HansV]

Basically, Clock uses Application.OnTime to run itself again after one second.

The rest is mostly to ensure that the code stops gracefully when the form is closed and/or the workbook is closed.

[Nick Vittum]

Basically, Clock uses Application.OnTime to run itself again after one second.. . .

Ahh. So with the loop, it was —looping— continuously, but with OnTime, it's stopping/restarting every second. And if I delete "ss", I could have it restart every 60 seconds. Would that make any appreciable difference in processor or application load?

[snb]

You'd better replace all 'select' and 'Activate' for educational purposes.

[HansV]

If you want to update the clock every minute, you can use DateAdd("s", 60, Now) or DateAdd("n", 1, Now) - for minutes, we use "n" to avoid confusion with "m" for months.
Updating every minute will make Excel respond much better than if you update every second.

[Nick Vittum]

You'd better replace all 'select' and 'Activate' for educational purposes.

I may well do that. But it's all educational.

[Nick Vittum]

If you want to update the clock every minute, you can use DateAdd("s", 60, Now) or DateAdd("n", 1, Now) - for minutes, we use "n" to avoid confusion with "m" for months.
Updating every minute will make Excel respond much better than if you update every second.

Great!