Updating NOW() in Excel

  Tycho 17:40 22 Sep 06
Locked

I am using the NOW() function in one of my spreadsheets. I would like it to update continuously but I can't get the sheet to recalculate without hitting f9 or altering it in some way.

Is there any way of automating the update so that it reapeats frequently?

IN hopes

T

  VoG II 17:47 22 Sep 06

This macro will display the time in cell A1. You can format A1 to show your desired display. If you want this to run every time you open a workbook rename it to Auto_Open.

Code:

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

To use it, ALT+F11 to open the Visual Basic Editor, Insert > Module. Copy the code above then paste it into the module. Exit the VBE. Tools > Macro > Macros, highlight TickTock and click the Run button.


This is a good example of recursion - a program that calls itself.

  Tycho 17:49 22 Sep 06

Spot on!

Thanks VoG

  Simsy 18:05 22 Sep 06

There may be better ways, but the following will work;

Open the vis Basic editor (alt& F11)

Select "This workbook" and

Workbook Open

Insert the following code;

Private Sub Workbook_Open()
UpdateTrigger
End Sub

(i.e. insert the single line between the two that appear by default)

Then, still in the editor, from thje menu choose Insert>Module

and paste the following;


Public Sub UpdateTrigger()

Application.OnTime Now + TimeValue("00:00:01"), "UpdateCell"

End Sub


Public Sub UpdateCell()

Application.Calculate

UpdateTrigger

End Sub


Save the workbook.


When you reopen it the first Instruction will run. This tells the UpdateTrigger macro to run...

What this does is run the UpdateCell macro...

This forces Excel to recalculate and also runs the Update trigger again...

and so on, and on.


Note that I've cobbled this quickly, and I haven't tested it much. I'm sure there may be events that can cause it to crash or go wrong, ... but you get the idea. I don't see how you can do it without a macro.

(The figure (00:00:01) means that it does things every second.)


I hope it helps,

Regards,

Simsy

  Simsy 18:08 22 Sep 06

that VoG™ would beat me to it!

And provide a better solution.

One of the things I hadn't thought of is that mine doesn't want to shut down!!

Ignore mine!

Regards,

Simsy

  VoG II 18:12 22 Sep 06

Actually, Simsy, mine won't shut down either :o(

The only way that I've managed to stop it is to delete the code in the VBE. It then throws a wobbly and stops!

  VoG II 18:47 22 Sep 06

This is an improvement

Code:

Dim NextTick As Date
Sub TickTock()
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


Use StopClock to stop the time display and allow you to save the workbook and Exit Excel!!

  Simsy 20:18 22 Sep 06

at work, for a specific purpose on budget day, and had no problems with it.

I'm back at work in the early morning... I'll look at it then and repost with the appropriate code!

Regards,

Simsy

  Simsy 08:40 23 Sep 06

I am having the same problem here at work. I don't remember it being a problem at the time, but I guess as it was for a very specific thing, and was only going to be used once, it didn't matter...

I have discoverd however, if when closing you get the enable/disable macros box, clicking on the X in the top right closes it cleanly, whether you've chosen to save changes or not. Obviously it's not an elegant "solution", but may suffice depending on needs.

The other thing that strikes me as useable is to put the "stop" code in a "BeforeClose" or "BeforeSave" event.

Regards,

Simsy

This thread is now locked and can not be replied to.

Surface Pro (2017) vs Surface Pro 4

Where HTML5 is headed next

MacBook Pro v Surface Pro 5