Forum Discussion
VBA Code to Count Down Days
Let's say the target date is in cell D2 on Sheet1.
In the ThisWorkbook module:
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
Thanks - you are a genius!
How can I amend the above to make it count only working days from TODAY(), as each day refreshes please?
Here is a version that uses the number non-weekend days:
Private Sub Workbook_Open() Dim TargetDate As Date Dim NumberOfDays As Long TargetDate = Worksheets("Sheet1").Range("D2").Value NumberOfDays = Application.NetworkDays(Date, TargetDate) - 1 If NumberOfDays > 0 Then MsgBox "Number of Days to Compliance: " & NumberOfDays, vbInformation End If End SubThanks so much.
I've copied and pasted this into VBA and when I click run it keeps asking me for a macro name (which I seem to need to create) - am I doing something wrong? It also doesn't pop up when I first open the workbook. I have to manually run the macro to see the days countdownIn the Visual Basic Editor, look at the Project Explorer pane on the left hand side.
Under your workbook, you should see Microsoft Excel Objects, and under that, you should see ThisWorkbook.
Double-click ThisWorkbook and copy/paste the code into the module that appears.
Switch back to Excel and save the workbook. Make sure that it's a macro-enabled workbook (*.xlsm).
Next time you open the workbook, you may have to enable macros.
The code should then run automatically.
The code subtracts Date from the target date.
Date is the VBA equivalent of TODAY(): it always returns the current date.
Oh wait - you want working days. Just a moment, please.