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.

 

[kumara_faith]

Hi,

This time it works but when I close the workbook, the workbook closes and immediate I get the macro message that we will get if I open the workbook (Enable macro or Disable macro message).

If a choose enable macro, it automatically opens the workbook again. If a choose disable macro, the same thing happens.

Appreciate your guidance and again thank you for your patience.

 

[VoG]

The only way to get around that is to lower your macro security settings. It isn't possible to do that programatically, for obvious reasons.

 

[kumara_faith]

Hi,

I went to Tools>>Macro>>Security and selcted "Low" .

Now, when I close the workbook, it doesnt prompt the message but intead opens the workbook again.

Is there a workaround this ? Thanks.

 

[VoG]

That suggests that the StopClock sub isn't working and I don't know why (as it clearly was before). Are you sure that you have the Workbook_BeforeClose event in the ThisWorkbook's code module.

As a workaround you can run the StopClock sub before closing the workbook.

 

[kumara_faith]

Hi,

Just to ensure, here is the current code I am using:

On Module 1

Sub TickTock()
ThisWorkbook.Sheets("Sheet2(POI_Simulation_Test").Range("A1").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

On ThisWorkbook

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

Private Sub Workbook_Open()
Call TickTock
End Sub

How do I run the StopClock sub before closing the workbook ?

Thanks.

 

[VoG]

You have a typo here

SThisWorkbook.Sheets("Sheet(POI_Simulation_Test").Range("C1").Value = Now()

which should be

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

To run StopClock Tools > Macro > Macros, highlight StopClock and click Run.

 

[kumara_faith]

Hi,

The following is what I am using now:

Sub TickTock()
ThisWorkbook.Sheets("Sheet(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

And then I went to Tools > Macro > Macros, highlight StopClock and click Run.

When I open the workbook, I get the following message:

Run Time Error 9
Subscript Out Of Range

When I debug, the following is highlighted:

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

My apologies for all the inconvenience. Appreciate your patience in this one.

 

[VoG]

As I said before, this is wrong

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

It should be

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

 

[kumara_faith]

Hi,

My apologies. I have modified the codes as below:

Module 1

Sub TickTock()
ThisWorkbook.Sheets("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

ThisWorkbook

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

Private Sub Workbook_Open()
Call TickTock
End Sub

And then I went to Tools > Macro > Macros, highlight StopClock and click Run.

I have also set the macro security to Low.

When I close the workbook and open it again, it works.

However when I want to close it , it doesn't prompt any message but reopen or goes back to the spreadsheet.

Again, I am sorry for taking up much of your time. Appreciate your tolerance and patience.

 

[VoG]

I can only suggest that you close Excel then re-open your workbook.

If that doesn't work then I'm stumped.