• Status: Solved
  • Priority: Medium
  • Security: Private
  • Views: 19
  • Last Modified:

Update Today() Automatically At Midnight

I have a spreadsheet that I rarely close.
It uses the TODAY() function, and I have to update it manually (F9) every day.

How can I have it update itself automatically every midnight?
0
Eirman
Asked:
Eirman
  • 5
  • 3
1 Solution
 
byundtCommented:
You can force a worksheet to calculate when the workbook opens using a WorkbookOpen event sub. That sub can then use Application.OnTime to call itself every evening at midnight.
'All code goes in ThisWorkbook code pane
Private Sub Workbook_Open()
UpdateAtMidnight
End Sub

Private Sub UpdateAtMidnight()
Dim ws As Worksheet
ThisWorkbook.Worksheets(1).Calculate    'First worksheet in workbook
'Sheet1.Calculate                       'Using code name (not necessarily the same as what you see on the sheet tab)
'Worksheets("Sheet1").Calculate         'Using the tab name (what you do see on the sheet tab)
Application.OnTime Date + 1, "ThisWorkbook.UpdateAtMidnight"
End Sub

Open in new window

0
 
EirmanChief Operations ManagerAuthor Commented:
There is only sheet at the moment.
When I unlock my PC in the morning, will my sheet have updated itself?
Sometimes it updates anyway ...... I haven't worked out exactly when.
0
 
byundtCommented:
Statement 8 forces a recalculation of the first worksheet (and only worksheet in your particular workbook). Statement 11 schedules that recalc macro to run at midnight on a continuing basis.

I don't know if Application.OnTime will wake up a sleeping computer. If not, the code will run as soon as the computer awakes--such as when you unlock the PC in the morning.

The only way to prevent Application.OnTime from running is to quit Excel. For this reason, I should have given you a routine to turn the timer off when the workbook is closed. Otherwise, Excel will reopen the workbook (if necessary) to run the macro. This sub should also go in the ThisWorkbook code pane.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime Date + 1, "UpdateAtMidnight", , False
End Sub

Open in new window


It is worth noting that there may be at most one Application.OnTime timer in effect on an application-wide basis. If you have other workbooks with macros that use Application.OnTime, you will need to add logic to co-ordinate their calls.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
EirmanChief Operations ManagerAuthor Commented:
I'll let you know how it works overnight
0
 
EirmanChief Operations ManagerAuthor Commented:
So far so good .......
I'll report back on Monday.
0
 
EirmanChief Operations ManagerAuthor Commented:
Hi byundt

I have attached the spreadsheet in question. Have I applied your code correctly?
Nothing happens at midnight.

HOWEVER, when I do virtually anything, the purple  XXXX__________________XXXX
updates itself. Conditional formatting seem be very 'aware'.

BUT, to get the dates on top or column Q to update, I have to F9

It's the same when I manually change the clock
PLANNERdataRemoved.xlsm
0
 
byundtCommented:
Eirman,
You put the suggested code in the wrong place. As a result, the timer is not set when the workbook opens, and you will get an error message if you try running UpdateAtMidnight manually.

The code must go in the ThisWorkbook code pane rather than a regular module sheet. If you look at the Project Explorer (use View...Project Explorer on VBA menu if you don't see it in a pane to the left of the VBA code), doubleclick ThisWorkbook where it is listed underneath your worksheets and above Module1.

Cut the two suggested subs from Module1 and paste them in ThisWorkbook code pane. I have done this for you in the attached workbook.

Brad
PLANNERdataRemovedQ28656809.xlsm
0
 
EirmanChief Operations ManagerAuthor Commented:
Four minutes past midnight  ... and it worked!
Many Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now