Date and Time on worksheet

do u want a macro that will run/update every 5 seconds? if so, then here it is. You will have to insert your macro at the Doit Sub

Dim ThisTime As Date

Sub StartDoingIt()
DoItAgain
End Sub

Sub DoItAgain()
ThisTime = Now + TimeValue("0:00:05")
Application.OnTime ThisTime, "DoItAgain"
DoIt
End Sub

Sub DoIt()

'insert your macro here

End Sub

Sub StopDoingIt()
On Error Resume Next
Application.OnTime ThisTime, "DoItAgain", schedule:=False
End Sub

Sub Auto_Close()
StopDoingIt
End Sub

In cell A1 on your worksheet enter:

=NOW()

press Enter and format it as "hh:mm:ss".

Right click the XL icon to the left of File on the menu and choose View Code. Paste this into the window on the right:


Note that the first and last lines may already be present.

Click your workbook in the Project window and choose Insert, Module from the menu. Paste this code in th window on the right:


Press Alt+F11 to return to your workbook. Save and close your workbook, then re-open it.

The time in cell A1 will change every second. To use a different cell or worksheet change the references in the first line of code in procedure UpdateClock. You can also change the update interval by amending the argument for the TimeValue function.

Andrew, you're just too classy for words

Thx it works

On 2002-12-12 06:25, majinfaisal wrote:
Thx it works

Click to expand...

Well it doesn't for me. I've just spent the last half hour trying to close the workbook - it keeps reopening. Still working on a fix.

Andrew

I was pretty impressed with that code. Shame it's causing problems.
I tried this sequence, and it seems to stop the update, but I don't know if it will fix your problem. I assume it is because UpdateClock is still running.

Dim ReadyToClose As Boolean

Sub UpdateClock()
' *** Change Sheet name and Range reference to suit ***
If ReadyToClose = False Then
Worksheets("Sheet2").Range("A1").Calculate
Application.OnTime Now + TimeValue("00:00:01"), "UpdateClock"
End If
End Sub

Sub StartUpdate()
'this might go in Workbook _Open
Application.OnTime Now + TimeValue("00:00:01"), "UpdateClock"

End Sub
Sub StopUpdate()
'this might go in the Workbook_BeforeClose
ReadyToClose = True
End Sub

.... but maybe you tried that

Expel,

Yeah, I've been trying to stop the timer in the Workbook_BeforeClose event and I've put a boolean flag in the UpdateClock procedure. I can stop it manually, but not when I close the workbook.

Still playing.

Andrew, there's a workbook here that I downloaded a while ago which does a similar thing. From the code, it looks like you need to set the Saved property to True on the way out, maybe? Edit- actually no, just need the disable Sub as below, link - http://www.xl-logic.com/pages/vba.html<pre>
Dim SchedRecalc As Date

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

Sub Recalc()
Range("Time").Value = Now
Call SetTime
End Sub

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

Private Sub Workbook_Open()
Call SetTime
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call Disable
ActiveWorkbook.Saved = True
End Sub</pre>
This message was edited by Mudface on 2002-12-12 07:36

Andrew

I just called StopUpdate from the Before_Close event, then when the book asked to save on close I cancelled, and the clock was stopped.

It doesn't work if the variable is altered from the Before_Close event.