Need a excel formula which can see the second moving

[wilchong via OfficeKB.com]

Dear sir,
I set an excel formula =NOW() which show the time. However, the time will
only refresh when I hit the formula.

My question is that is there possible to set a excel formula which we can see
the second (ss) moving?

Many thanks,
Wilchong

 

[Gary''s Student]

You can use an OnTime event macro to perform periodic re-calculations. See:

http://www.cpearson.com/excel/OnTime.aspx

 

[CurlyDave]

Here's a VBA code Slightly modified from this site
http://www.mrexcel.com/forum/showthread.php?t=86297

Dim SchedRecalc As Date
Sub Clock()
Dim wbk As Workbook
Dim ws As Worksheet

Set wbk = ThisWorkbook
Set ws = wbk.Sheets("Sheet1")

ws.Range("A1").Value = Format(Now, "dd-mmm-yy")
ws.Range("B1").Value = Format(Time, "hh:mm:ss AM/PM")

Call SetTime
End Sub
Sub SetTime()
SchedRecalc = Now + TimeValue("00:00:01")
Application.OnTime SchedRecalc, "Clock"
End Sub

Sub Disable()
On Error Resume Next
Application.OnTime EarliestTime:=SchedRecalc, Procedure:="Clock",
Schedule:=False
End Sub

 

[Shane Devenshire]

Hi,

A repeating sub routine
==========================

Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:00:15"), "my_Procedure"
End Sub

Sub my_Procedure()
Application.OnTime Now + TimeValue("00:00:15"), "my_Procedure"
MsgBox "Hi"
End Sub

This one repeats every 15 second, modify it as necessary.

 

[wilchong via OfficeKB.com]

Dear Curly Dave,
Many thanks for your suggested Macro which you given me a few day ago. The
clock will running ONLY after I activitate the Macro program. However, I
found one thing interestingly is that the excel file with this clock macro
program is very difficult to close. I need to try many time. At the
beginning, I think I need to terminate the macro program before I close the
file, but I don't how to terminate the macro program.

My question is that how can I close the file with the clock macro program
like usual?

Many thanks,
Wilson

 

[CurlyDave]

Yes I see....

Try this again

Dim SchedRecalc As Date
Sub Recalc()
Dim wbk As Workbook
Dim ws As Worksheet

Set wbk = ThisWorkbook
Set ws = wbk.Sheets("Sheet1")

ws.Range("A1").Value = Format(Now, "dd-mmm-yy")
ws.Range("B1").Value = Format(Time, "hh:mm:ss AM/PM")

Call SetTime
End Sub
Sub SetTime()
SchedRecalc = Now + TimeValue("00:00:01")
Application.OnTime SchedRecalc, "Recalc"
End Sub

Sub Disable()
On Error Resume Next
Application.OnTime EarliestTime:=SchedRecalc, Procedure:="Recalc",
Schedule:=False
End Sub

Use the disable macro to stop the clock

Also in the workbook close event you can disable the macro

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Disable

End Sub

Start the clock when you open the workbook

Private Sub Workbook_Open()
Recalc
End Sub
the last two codes go in the workbook module

 

[wilchong via OfficeKB.com]

Dear Curly Dave,
First of all, thanks for your suggestion again. Secondly, I am not a Macro
user, another word, I am not even know a single macro program. As a result,
excel formular is very reliable for me. I am wondering that instead of using
macro program to make the clock running, do you think excel formular also can
make clock running?

Thirdly, the "Disable" mocro program you gave me yesterday cannot work well
because my PC told me that there is syntax problem in macro (The computer
highlight this syntax: Application.OnTime EarliestTime:=SchedRecalc,
Procedure:="Recalc"),.

Many thanks for your advice,
Wilchong

 

[CurlyDave]

Sure, use this formula
=TEXT(NOW(),"m/d/yyyy h:mm:ss")

then press the F9 Key to update the time, the F9 key tells xl to
calculate.
The sheet will also calculate every time you enter an item into the
sheet if you have calculation set to automatic.

Your disable code did not work because

Application.OnTime EarliestTime:=SchedRecalc, Procedure:="Recalc",
Schedule:=False

is all one line. you need to place
Schedule:=False
to the end of the line above it.
Sometimes when pasting code to the forum the lines get split.