Update Time Automatically

K

kumara_faith

Well-known Member
Joined
Aug 19, 2006
Messages
953
Office Version
  1. 365
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.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
I think the code in the link that you posted should work. If not try this. In a regular module:

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

Code: 
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.
 
Upvote 0
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 ?
 
Upvote 0
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?
 
Upvote 0
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()
 
Upvote 0
Ah! Change that line to

Code: 
ThisWorkbook.Sheets("Sheet1").Range("A1").Value = Now()
 
Upvote 0
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.
 
Upvote 0
Change the StopClock code to the following

Code: 
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.
 
Upvote 0
Hi,

I changed the codes as follows:

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


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

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


Appreciate your assistance.
 
Upvote 0
I think that you have wrongly specified the sheet name. Try

Code: 
ThisWorkbook.Sheets("POI_Simulation_Test").Range("C1").Value = Now()
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Automatically update textboxes on userform
Replies
4
Views
231
DanteAmor
DanteAmor
O
  • Question Question
Automatically update a file when a cell is changed
Replies
2
Views
123
Joe4
Joe4
B
  • Question Question
VLOOKUP Update values pop-up
Replies
0
Views
130
boxboxbox
B
M
  • Solved
How to move custom VBA/ribbons
2
Replies
12
Views
597
missingsc
M
N
  • Question Question
Spreadsheet with lots of formulas and named ranges takes time to load and clocks each time a cell is updated
2
Replies
10
Views
126
NSRL
N

Forum statistics

Threads
1,224,527
Messages
6,179,348
Members
452,907
Latest member
Roland Deschain

Share this page

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
Back
Top