Updating values when the system date changes

Hi Gene,

I assume you are using the Today() function to give the system's date. Unfortunately (at least in your case) the Today() function will not calculate or update automatically when the date changes, but only when a sheet calculation is called for via a cell dependency or manual recalculation.

If you want the workbook to update the date the instant it changes (i.e., at midnight), you will need to use the OnTime event to force a sheet recalculation to always be done at midnight. To do this, run this line of code:

Application.OnTime Date()+1, "RecalcSheet"

where this schedules the RecalcSheet macro to be run at 00:00:00 then next day. The RecalcSheet macro is just:

Sub RecalcSheet()
Worksheets("Sheet1").Calculate
End Sub

I hope this helps.

Happy computing.

Damon

Of course, if Excel is not running at midnight the recalculation cannot occur, so you should also set up Excel so it recalculates the sheet each time the workbook is opened by putting Worksheets("Sheet1").Calculate in the ThisWorkbook Open event. The OnTime command above should also be placed in the ThisWorkbook Open event so that it gets scheduled as soon as the workbook is opened.