Updating now() automatically

[BENSONJR]

UPDATING NOW() AUTOMATICALLY


I have the =NOW() formula in several worksheets. How can I have them automatically updated, let’s say every 5 seconds? Thank you very much for your help . . .

Please also see my question about the code I cannot run. Thanks again

 

[dannywilliams]

Hello Benson

I can do it by assigning a start clock and stop clock button like this

Sub TimerMe()
Range("B1").Value = 1
Do While Range("B1").Value = 1
Application.OnTime Now + _
TimeValue("00:00:01"), "Refresh"
Exit Do
Loop
End Sub
Sub Refresh()
Calculate
If Range("B1").Value = 1 Then
TimerMe
Else
Exit Sub
End If
End Sub

Sub stopClock()
Range("B1").Value = 0
End Sub

But that seems really resource heavy to have running all of the time. If I think of anything else I will post later

Danny

 

[Akashwani]

Hi Benson, try this..

In your workbook put this code...

Dim bolOpening As Boolean
Dim t As Date
Private Sub Workbook_Open()
Flag = True
    RunWhen = Now + TimeSerial(0, 0, 1)
    Application.OnTime RunWhen, "UpdateClock"
t = Now
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Flag = False
    Call StopClock
End Sub

In a standard module put this...

Option Explicit
Public Flag As Boolean
Public RunWhen As Double
Sub UpdateClock()
    If Flag = True Then
'       *** Change Sheet name and Range reference to suit ***
        Worksheets("Search").Range("O2").Calculate
        RunWhen = RunWhen + TimeSerial(0, 0, 1)
        Application.OnTime RunWhen, "UpdateClock"
    End If
End Sub
Sub StopClock()
    On Error Resume Next
    Application.OnTime EarliestTime:=RunWhen, Procedure:="UpdateClock", Schedule:=False
End Sub

This will update your clock every second, I hope it helps.

Ak