Forum Discussion
VBA Code to Count Down Days
Hi All I want to create a message box that pops when someone opens an excel file and it should read: "Number of Days to Compliance: XXX" I can create the message box in VBA but don't know...
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 SubThanks - 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 countdown
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.
