Timer

  • Thread starter Thread starter Don Lloyd
  • Start date Start date
D

Don Lloyd

Hi,

A cell displays the current date and time, which does not refresh if nothing
is done.

How can I arrange for the cell to be up-dated automatically at set intervals
e.g. every minute.

Don

--
 
You would have to use a programmatic solution. Put the following in a
standard VBA module.

Option Explicit

Dim NextTime As Date
Sub RepeatOneSec()
Application.Calculate
NextTime = Now() + TimeSerial(0, 0, 1)
Application.OnTime NextTime, "RepeatOneSec"
End Sub
Sub EndProcess()
Application.OnTime NextTime, "RepeatOneSec", , False
End Sub

Change the TimeSerial() parameters to suit. Currently, it updates the
XL environment every second.

Once done, run the RepeatOneSec macro. Run the EndProcess macro to stop
the automatic updates.

--
Regards,

Tushar Mehta
MS MVP Excel 2000-2003
www.tushar-mehta.com
Excel, PowerPoint, and VBA tutorials and add-ins
Custom Productivity Solutions leveraging MS Office
 
Thank you Tushar,

As expected, works fine.

Just one query.
If the routine is called on opening the workbook, is it necessary (or
advisable) to stop the timer before running other routines?

regards,
Don
--
 
Back
Top