Are there other functions beside NOW() that I can use to have the correct time displayed in a cell ?

What's wrong with the NOW() function that means you need a different one?

On Tuesday, September 11, 2012 3:36:34 PM UTC-4, (unknown) wrote:
Are there other functions beside NOW() that I can use to have the correct time displayed in a cell ?

For some reason it does not update all the time - there are pauses in the updates. So I'm looking for a time function that reflects the current time (hh:mm:ss) continuosly over the course of the day.

To get a cell (say B9) to display an updating time, run the StartClock macro listed below:

Dim PleaseStopMe As Boolean

Sub StopIt()
PleaseStopMe = True
End Sub

Sub StartClock()
PleaseStopMe = False
Do
Range("B9").Value = Format(Now, "hh:mm:ss")
DoEvents
If PleaseStopMe Then Exit Sub
Loop
End Sub

To stop the clock, run the StopIt macro

"Paga Mike" wrote in message
...

;1605440 Wrote:
On Tuesday, September 11, 2012 3:36:34 PM UTC-4, (unknown) wrote:-
Are there other functions beside NOW() that I can use to have the
correct time displayed in a cell ?-

For some reason it does not update all the time - there are pauses in
the updates. So I'm looking for a time function that reflects the
current time (hh:mm:ss) continuosly over the course of the day.

To get a cell (say B9) to display an updating time, run the StartClock
macro listed below:

Dim PleaseStopMe As Boolean

Sub StopIt()
PleaseStopMe = True
End Sub

Sub StartClock()
PleaseStopMe = False
Do
Range("B9").Value = Format(Now, "hh:mm:ss")
DoEvents
If PleaseStopMe Then Exit Sub
Loop
End Sub

To stop the clock, run the StopIt macro


The StartClock macro provides a solution to the specific request of the OP
(ie: "continuosly over the course of the day"). It is, however, a
continuous, iterative loop that, once started, will never end. Once
StartClock begins, you cannot stop it by running StopIt because StartClock
is still running its endless loop.

Hi all

I use this nice chunk of code which works very nicely, essentially this
saves the workbook to 2 locations ( 2 is a backup ), it also stamps ( C2
) with the time it was last saved so you have a visual reference of it.

If you want to trogger some other type of event, simply replace the
SaveBook routine, and or the timer frequency with your desired timeframe
in which to fire.


Put this in the "ThisWorkbook"

Private Sub Workbook_Open()
StartTimer
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
StopTimer
End Sub

Then in a Module, use this to start the timer, it is currently set to
trigger ever 30 minutes..

Option Explicit
Public RunTime

Sub StartTimer()

RunTime = Now + #12:30:00 AM#

Application.OnTime RunTime, "SaveBook", schedule:=True

End Sub



Sub SaveBook()

StartTimer

ChDir "C:\"
ActiveWorkbook.SaveAs Filename:= _
"C:\WowSchedMaster.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ChDir "T:\WOW VIC\wow scheduler"
ActiveWorkbook.SaveAs Filename:="T:\WOW VIC\wow
scheduler\WowSchedMaster.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

ActiveSheet.Select
Range("C2").Select
Selection.NumberFormat = "hh:mm"
Selection.Value = Now()

End Sub

And finally, this stops the timer...

Sub StopTimer()

On Error Resume Next
Application.OnTime RunTime, "SaveBook", schedule:=False

End Sub


HTH
Mick.

Il 12/09/2012 17:50, ha scritto:
On Tuesday, September 11, 2012 3:36:34 PM UTC-4, (unknown) wrote:
Are there other functions beside NOW() that I can use to have the correct time displayed in a cell ?

For some reason it does not update all the time - there are pauses in the updates. So I'm looking for a time function that reflects the current time (hh:mm:ss) continuosly over the course of the day.


Put this in the "ThisWorkbook":

Private Sub Workbook_Open()
StartUpdate
End Sub

Then create a Module and put the

Sub StartUpdate()
Application.OnTime Now + TimeValue("00:00:01"), "StartUpdate"
Calculate
End Sub

Hi,
E.

Actually, you can stop it by running the StopIt macro. The DoEvents statement allows the user to share focus with the macro.

Try it