Show Days Until a Compliance Date When an Excel Workbook Opens
Question details
Create a VBA message that displays the number of days remaining until a compliance date each time the workbook opens.
- Product
- Microsoft Excel workbook with VBA macros
- Device & OS
- not provided
- Scenario
- A compliance deadline is stored in a worksheet cell, and the workbook needs to notify users of the remaining time on startup.
- Observed behavior
- The desired result is an opening message box that calculates the difference between the stored compliance date and the current date.
Make sure the compliance date cell contains a valid Excel date, such as the date in Sheet1!D2. Save the workbook as a macro-enabled workbook (.xlsm) and enable macros when opening it.
Add a Workbook_Open macro to display the compliance countdown
Use this method when you want Excel to calculate and display the remaining days automatically whenever the workbook opens.
The Workbook_Open event must be placed in the ThisWorkbook code module, not in a standard module or worksheet module. This example reads the deadline from cell D2 on Sheet1 and shows a message only before the deadline.
In Excel, press Alt+F11 to open the Visual Basic Editor.
In the Project Explorer pane, expand your workbook project and double-click ThisWorkbook.
Paste the following code into the ThisWorkbook code window: Private Sub Workbook_Open() Dim TargetDate As Date TargetDate = Worksheets("Sheet1").Range("D2").Value If TargetDate > Date Then MsgBox "Number of Days to Compliance: " & TargetDate - Date, vbInformation End If End Sub
Select File > Save As and save the file as an Excel Macro-Enabled Workbook (*.xlsm). Close and reopen the workbook, then select Enable Content if Excel displays a macro security prompt.
Handle dates that are due today or already overdue
Use this variation if users should receive a clear message on the deadline date and after the compliance date has passed.
Create a compliance date countdown macro in WPS Spreadsheets
WPS Spreadsheets provides a familiar worksheet and macro workflow for creating a startup reminder that reads a compliance date, calculates the days remaining, and displays a message when the file opens. You can work with macro-enabled spreadsheet files while keeping a Microsoft Office-compatible format.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, select Spreadsheets, and open the workbook containing the compliance date in Sheet1!D2.
- 2. Access macro tools: Go to the Developer tab and select Visual Basic or Macro tools to open the workbook's macro editor.
- 3. Add the startup procedure: Open the ThisWorkbook project module and add a Workbook_Open procedure that reads Sheet1!D2 and compares it with Date.
- 4. Save as a macro-enabled workbook: Select Menu > Save As and choose the Excel Macro-Enabled Workbook (*.xlsm) format to preserve the macro.
Frequently Asked Questions
Where should I put a Workbook_Open macro in Excel?
Put the procedure in the ThisWorkbook code module. Press Alt+F11, locate your workbook in Project Explorer, and double-click ThisWorkbook before pasting the code.
Why does my compliance message not appear when I open the workbook?
Confirm that the file is saved as .xlsm, macros are enabled, the procedure is named Workbook_Open, and the code is in ThisWorkbook rather than Module1 or a worksheet code module.
How do I change the cell that stores the compliance date?
Update the worksheet and range reference in the macro. For example, change Worksheets("Sheet1").Range("D2") to Worksheets("Compliance").Range("B5") for a date stored in Compliance!B5.
Can the message show when the deadline is overdue?
Yes. Add an ElseIf TargetDate = Date condition for a due-today message and an Else condition using Date - TargetDate to display the number of overdue days.
Does the time in a date cell affect the number of remaining days?
The Date function uses today's date without a time value. If the target cell includes a time, use DateValue(TargetDate) in the calculation when you want to ignore the time portion.
