If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Need a excel formula which can see the second moving
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 -- Message posted via http://www.officekb.com |
#2
|
|||
|
|||
Need a excel formula which can see the second moving
You can use an OnTime event macro to perform periodic re-calculations. See:
http://www.cpearson.com/excel/OnTime.aspx -- Gary''s Student - gsnu2007L "wilchong via OfficeKB.com" wrote: 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 -- Message posted via http://www.officekb.com |
#3
|
|||
|
|||
Need a excel formula which can see the second moving
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, Procedu="Clock", Schedule:=False End Sub |
#4
|
|||
|
|||
Need a excel formula which can see the second moving
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. -- If this helps, please click the Yes button Cheers, Shane Devenshire "wilchong via OfficeKB.com" wrote: 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 -- Message posted via http://www.officekb.com |
#5
|
|||
|
|||
Need a excel formula which can see the second moving
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 wrote: 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, Procedu="Clock", Schedule:=False End Sub -- Message posted via http://www.officekb.com |
#6
|
|||
|
|||
Need a excel formula which can see the second moving
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, Procedu="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 |
#7
|
|||
|
|||
Need a excel formula which can see the second moving
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, Procedu="Recalc"),. Many thanks for your advice, Wilchong CurlyDave wrote: 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, Procedu="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 -- Message posted via http://www.officekb.com |
#8
|
|||
|
|||
Need a excel formula which can see the second moving
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, Procedu="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. |
Thread Tools | |
Display Modes | |
|
|