Inserting a Clock

Slythe

New Member
Joined
Jan 16, 2005
Messages
10
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
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

ExcellVBUser

New Member
Joined
Jan 20, 2005
Messages
24
'Paste this into ThisWorkBook(code)

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)

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

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,511
Messages
5,596,581
Members
414,079
Latest member
Frills

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top