Date and Time on worksheet

[majinfaisal]

How do i enable a date and time function to appear on the worksheet - so it updates constantly (either minutes or seconds). If this isn't available can anyone tell me website that contains the UK date and time and updates it constantly, I have tried the web query with http://www.sky.com but for some reason the date doesn't come up.

 

[Paul B]

Here is a good example using a chart for the clock
http://j-walk.com/ss/excel/files/clockchart.htm

 

[Andrew Poulsom]

Here is an update:

' ThisWorkbook module

Option Explicit

Private Sub Workbook_Open()
    Flag = True
    RunWhen = Now + TimeSerial(0, 0, 1)
    Application.OnTime RunWhen, "UpdateClock"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Flag = False
    Call StopClock
End Sub

' General module

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("Sheet1").Range("A1").Calculate
        RunWhen = RunWhen + TimeSerial(0, 0, 1)
        Application.OnTime RunWhen, "UpdateClock"
    End If
End Sub

Sub StopClock()
    Application.OnTime EarliestTime:=RunWhen, Procedure:="UpdateClock", Schedule:=False
End Sub

I have discovered that the problems I had were caused by an Add-In I am using. It seems to interfere with the unscheduling of the procedure.

 

[alan88888]

Hi just joined here as i am trying to put a clock in a worksheet....i have followed Andrews directions and they work great, but as soon as i minimise the workbook i get a runtime error..

Any ideas??

 

[Chitosunday]

In the module
Public timeevent
Sub UpDateClock()
' *** Change Sheet name and Range reference to suit ***
Worksheets("Sheet1").Range("A1").Calculate
timevent = Now + TimeValue("00:00:01")
Application.OnTime timevent, "UpdateClock"
End Sub

in the workbook
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime timevent, "UpdateClock", False
ThisWorkbook.Saved = True
End Sub

 

[WaxonWaxov]

As an update to this five-year-old thread, Andrew's code labeled "here's an update" works great in Excel 2007 as long as you save the file as an macro-enabled workbook (.xlsm)

 

[bchico]

Sorry to bring up a dead thread but I just used Mr. Poulsom's code and it works amazing except for the fact that my screen flickers every second.

Is there a solution such as Application.Screenupdating = false? By using that code my screen obvioulsy doesnt update...I was just wondering if there was a similar solution that would stop the flickering.

Thanks.

 

[mlo356]

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

Hi All. Mudface, is the code you provided complete? The site is no longer available and for some reason I cant get it to work. Thinking that there may be more to the "Sub Recalc()" section.

Thanks!

 

[Richard7]

I tried to implement your code but can't get it to work. Does it go into "This Workbook"? If so, will it run automatically once the spreadsheet is opened?

Thanks!