NOW() Function PROBLEM SOLVED, NICELY TOO

[elgringo56]

When using the NOW() function, the date and time are displayed, but the time remains static from that point on. Is there a function that will allow the date and time to display and the time to incriment with the clock?
This message was edited by elgringo56 on 2002-08-22 23:37

 

[brettvba]

or put it in the status bar

Sub Z()
Application.OnTime now + TimeValue("00:00:1"), "Z"
Calculate
x = Format(now(), "hh:mm:ss")
Application.StatusBar = x
End Sub

Brett

 

[brettvba]

On 2002-08-22 21:07, Jay Petrulis wrote:
Hi Brett,

Nice job.

Another option to try...

<pre>Sub test()

With Application
.OnTime Now, "test"
.Caption = Now
End With
End Sub</pre>

Nice one see above

 

[elgringo56]

On 2002-08-22 21:04, brettvba wrote:
Well This will do it and leave you working give it a try.

Sub Z()
Application.OnTime now + TimeValue("00:00:1"), "Z"
If Range("a1").Value <> now() Then Calculate
End Sub


Brett

Thats pretty kewl, Brett. I had to add an ApplicationRun Z to my workbook open, but that works perfect. Thanks millions and Trillions.

 

[elgringo56]

Caution, dont put that in until the last thing you do because if you try and do a record macro with it in, it will show up in your macro and it will make it very hard to add code to the macor. But other than that, it works greag

 

[brettvba]

On 2002-08-22 23:21, elgringo56 wrote:

On 2002-08-22 21:04, brettvba wrote:
Well This will do it and leave you working give it a try.

Sub Z()
Application.OnTime now + TimeValue("00:00:1"), "Z"
If Range("a1").Value <> now() Then Calculate
End Sub


Brett

Thats pretty kewl, Brett. I had to add an ApplicationRun Z to my workbook open, but that works perfect. Thanks millions and Trillions.

To Tidy Up

Module

Sub Z()
Application.OnTime Now + TimeValue("00:00:01"), "Z"
If Range("a1").Value <> Now() Then Calculate
End Sub
Sub x()
Application.OnTime Now + TimeValue("00:00:01"), "Z", Schedule:=False
End Sub

ThisWorkbook Module

Private Sub Workbook_BeforeClose(Cancel As Boolean)
x
End Sub

Private Sub Workbook_Open()
Z
End Sub

The Sub Proceedure "x" will stop the on time proceedure,

Brett

 

[elgringo56]

Thanks for the update, Brett, So far this is doing just what I wanted it to. Thanks again.

 

[cokewithvanilla]

I know this is dragging up an old thread, but I used this methord (and it works), however, you cannot copy and paste unless you do it within a second. Is there any way to get this clock to update every minute instead of every second, or to gain back the copy/paste funtionality?

 

[shg]

It also cooks Undo. Why not instead use any desktop clock?

 

[cokewithvanilla]

It also cooks Undo. Why not instead use any desktop clock?

I can't use a desktop clock to change my conditional formatting. I have cells go yellow when someone is about to be done with a task and red when they should be finished.

 

[shg]

You could use OnTime to recalculate the workbook periodically, which could cause conditional formatting to reevaluate. Less glitz, but less impact.