Time-function Now() in Excel 2

[ocb]

I want to have a cell that shows the time right now, using the function now(), but I want the time to go on without having to "update" the worksheet all the time, e.g. the time and minute are right all the time, not only on an "update" or an "calculate now".

Any ideas how to make a cell show the time that updates continuously?

 

[KenWright]

You can't do that without locking up your spreadsheet (and Excel itself) with continuously running code.

Regards
Ken.................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[Hasit]

What are you trying to achieve? The updated time would only be visible if the spreadsheet was open - do you want the spreadsheet open all the time?

 

[ocb]

Yes - the spreadsheet will be open all the time, and I want the date and clock visible at the top of the spreadsheet, showing the right time at all time...

 

[Molby]

You can do this in the Status Bar.

Code from Combo a long time ago:

Place this in a standard module.

Sub xlTimer()
With Application
    .StatusBar = Format(Time, "hh:mm:ss")
    .OnTime Time + TimeValue("00:00:01"), "xlTimer"
End With
End Sub

You can start it in open procedure (Thisworkbook module):

Private Sub Workbook_Open()
Call xlTimer
End Sub

Set Application.statusbar=false after breaking code to restore status bar.

 

[combo]

If you just need a clock and tha taskbar one is not enough, use an old MS clock that can be found in win95 powertoys:

http://www.microsoft.com/windows95/downloads/contents/WUToys/W95PwrToysSet/Default.asp

There are other plenty of other free tools based on clock. You can consider to build hta application with java script that displays clock. The scripts can be found in some html tutors.

Excel is not the best tool to display current time. However it can be done, one of less disturbing solutions would be an add-in that uses api timer and displays a time in a menu item. As Ken pointed, you need VBA for this.

combo

 

[combo]

Oh, there are pople that read my old posts... I can recall another one, that uses callback timer: thread707-1205557

combo

 

[ocb]

But - I want to use it in a cell, because I want to use the time in antoher cell in a formula.

 

[ocb]

Fixed it:

Sub xlTimer()
With Application
.StatusBar = Format(Time, "hh:mm:ss")
.OnTime Time + TimeValue("00:00:01"), "xlTimer"
End With
Range("B2").Formula = "=Now()"

End Sub

Sub autpen()
Call xlTimer
End Sub

A question though, how do you show the "code" windows as shown with white background and black text?

 

[combo]

You could modify the procedure and, if necessary, the recalculation options (anyway, in general it is not a good practice to proceed this way):

Sub xlTimer()
With Application
.Calculate
.OnTime Time + TimeValue("00:00:01"), "xlTimer"
End With
End Sub

combo

 

[combo]

For formattong tips see 'Process TGML' link below the message window.

combo

 

[ocb]

testing code:

sub test

end sub