Update Time Automatically

[kumara_faith]

Hi,

Is is possible to have Excel update time automatically in a given cell within the spreadheet.

I have a spreadsheet that I need Excel to update the time automatically .

I would like Excel to update the time automatically in cell A1.

I have done some research and found the following thread but this only works when we close and open the spreadsheet.

http://www.mrexcel.com/archive2/70800/82079.htm

Is there a way to have Excel update the time without having to close the spreadsheet ?

Thanks.

 

[VoG]

I think the code in the link that you posted should work. If not try this. In a regular module:

Dim NextTick As Date

Sub TickTock()
Sheets("Sheet1").Range("A1").Value = Now()
NextTick = Now + TimeValue("00:00:01")
Application.OnTime NextTick, "TickTock"
End Sub

Sub StopClock()
Application.OnTime earliesttime:=NextTick, procedure:="TickTock", schedule:=False
End Sub

Then right click ThisWorkbook and select View Code. Enter the following.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call StopClock
End Sub

Private Sub Workbook_Open()
Call TickTock
End Sub

Format cell A1 on Sheet1 to Time hh:mm:ss.

When you open the workbook the timer will start and display the time in A1. Closing the workbook will turn off the timer.

 

[kumara_faith]

Hi,

I tried the codes and after a about 10- 20 seconds, I am getting the following error mesage:

Rub-time error "9":
Sunscript out of range


Just to ensure, in cell A1, I dont need to enter any formula right ? On format to hh:mm:ss ?

 

[VoG]

You don't need any formula in A1.

When the code errors do you get the option to debug? If so which line is highlighted?

 

[kumara_faith]

Hi,

I realised that the error message appears only when I switch or click to another excel workbook.

The following line is highlighted when I hit debug:

Sheets("Sheet1").Range("A1").Value = Now()

 

[VoG]

Ah! Change that line to

ThisWorkbook.Sheets("Sheet1").Range("A1").Value = Now()

 

[kumara_faith]

Hi,

It worked on a dummy spreadheet just to test the code.

But when I wanted to input this code to the actual spreadsheet, I am getting the following error message when I try to close the spreadsheet.

Run-time error"1004"
Method "On Time of object"_Application Failed

When I debug, I get the following line highlighted:

Application.OnTime earliesttime:=NextTick, procedure:="TickTock", schedule:=False

My actual spreadsheet has 3 sheets.

The first sheet is named "Master"
The second sheet is named "POI_Simulation_Test"
The third sheet is named "Scores"

I am trying to input the code for the second sheet named POI_Simulation_Test in cell C1.

I changed the codes from "Sheet1" to "Sheet2 (POI_Simulation_Test)" and I changed the range from "A1" to "C1".

Then I saved the code and went ot cell C1 in the POI_Simulation_Test sheet and formatted the cell to hh:mm:ss

I am new to VBA. Appreciate all your help and again thank you for your patience.

 

[VoG]

Change the StopClock code to the following

Sub StopClock()
On Error Resume Next
Application.OnTime earliesttime:=NextTick, procedure:="TickTock", schedule:=False
On Error GoTo 0
End Sub

By way of explanation, when you edited the macro it stopped the timer from running. When you closed the workbook the Workbook_BeforeClose event procedure ran the StopClock code which tried to stop the TickTock procedure but as TickTock wasn't running an error occurred. The above will fix that.

 

[kumara_faith]

Hi,

I changed the codes as follows:

Sub TickTock()
ThisWorkbook.Sheets("Sheet2(POI_Simulation_Test)").Range("C1").Value = Now()
NextTick = Now + TimeValue("00:00:01")
Application.OnTime NextTick, "TickTock"
End Sub

Sub StopClock()
On Error Resume Next
Application.OnTime earliesttime:=NextTick, procedure:="TickTock", schedule:=False
On Error GoTo 0
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call StopClock
End Sub

Private Sub Workbook_Open()
Call TickTock
End Sub

However, I am getting an error message as follows when close and open the workbook:

Run-time error "9"
Subscript out of range

When I hit debug, the following line is highlighted:

ThisWorkbook.Sheets("Sheet2(POI_Simulation_Test").Range("C1").Value = Now()

Appreciate your assistance.

 

[VoG]

I think that you have wrongly specified the sheet name. Try

ThisWorkbook.Sheets("POI_Simulation_Test").Range("C1").Value = Now()