Inserting a Clock

[Slythe]

Hi,

I dont suppose any of you kind kind excel folks know how to insert a runing clock into a spreadsheet to display the correct time?

Any help would be appriciated;
Regards Si

 

[ExcellVBUser]

'Paste this into ThisWorkBook(code)

Option Explicit

Private Sub Workbook_Open()
    'When This Workbook is opened, the Flag is changed to true
    'and the procedure "UpdateClock" is called.
    Flag = True
    Call UpdateClock
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, _
        ByVal Source As Range)
    ' runs when a sheet is changed and calculates range cells(only "C1" is used)
    Worksheets("Sheet1").Range("c1").Calculate
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    'When this workbook is going to close, it changes Flag to false,
    'Saves ThisWorkBook, and calls "StopClock"
    Application.OnTime timevent, "UpdateClock", False
    ThisWorkbook.Saved = True
    Flag = False
    Call StopClock
End Sub

'Make two buttons on Sheet1, then in cells "A1" and "C1" type
=Now()


'Then Paste this into Sheet1(code)

Option Explicit

Private Sub CommandButton1_Click()
    'This button changes Flag to true, and calls procedure "UpdateClock"
    Flag = True
    Call UpdateClock
End Sub

Private Sub CommandButton2_Click()
    'This button changes Flag to false, and calls procedure "StopClock"
    Flag = False
    Call StopClock
End Sub

'finally make a Module and Paste this

Option Explicit 'makes programmer declare all variables

'variables
Public Flag As Boolean
Public timevent As Double

Sub UpdateClock() 'This is the Main procedure, "UpdateClock"

'more variables
Dim tick As Integer
Dim tickcount As Integer

    If Flag = True Then
        'set starting values
        tick = Worksheets("Sheet1").Range("b1")
         If Worksheets("Sheet1").Range("b1") = "" Then
          tickcount = 1
         Else
          Worksheets("Sheet1").Range("b1") = Worksheets("Sheet1").Range("b1")
        End If

       '*** Change Sheet name and Range reference to suit ***
       'Next line down, calculates each cell in that range.
        Worksheets("Sheet1").Range("A1", "A2").Calculate
        'sets the time stamp for every 5 seconds.
        timevent = Now() + TimeValue("00:00:05")
        'this next line counts how many times the IF statement has looped.
        tickcount = tick + 1
        'Added if statment to reset tickcount to 0
'                If tickcount >= 7 Then
'                    tickcount = 0
'                End If
        Application.OnTime timevent, "UpdateClock"
        'Next line down puts the tickcount in cell "b1" on sheet1
        Worksheets("Sheet1").Range("b1") = tickcount

          
    End If
End Sub

Sub StopClock() 'This procedure stops the UpdateClock procedure.
    On Error Resume Next
    Application.OnTime EarliestTime:=timevent, Procedure:="UpdateClock", Schedule:=False
End Sub

Hope this helps

Marc

 

[Slythe]

wow, never expected it to be that complex, thanks for the help