Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Arrow Keys Moving Window Frame instead of Moving Between Cells | Excel Discussion (Misc queries) | |||
Moving Formula | Excel Discussion (Misc queries) | |||
moving a formula | Excel Discussion (Misc queries) | |||
Excel ? Formula for moving average | Excel Worksheet Functions | |||
Moving the formula bar | Excel Discussion (Misc queries) |