VBA Code to Count Down Days

Question

Hi All&nbsp;I want to create a message box that pops when someone opens an excel file and it should read:&nbsp;"Number of Days to Compliance: XXX"&nbsp;I can create the message box in VBA but don't know how to write the code to make the number of days between two dates also populate in the box.&nbsp; Can someone help please?&nbsp;Thanks

hansvogelaar · Answer

ClaireMcF&nbsp;
The code subtracts Date from the target date.
Date is the VBA equivalent of TODAY(): it always returns the current date.
&nbsp;
Oh wait - you want working days. Just a moment, please.

hansvogelaar · Answer

ClaireMcF&nbsp;
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 &gt; 0 Then
        MsgBox "Number of Days to Compliance: " &amp; NumberOfDays, vbInformation
    End If
End Sub
&nbsp;

hansvogelaar · Answer

ClaireMcF&nbsp;
In 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.
&nbsp;

&nbsp;
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.

hansvogelaar · Answer

ClaireMcF&nbsp;
Let's say the target date is in cell D2 on Sheet1.
In the ThisWorkbook module:
&nbsp;
Private Sub Workbook_Open()
    Dim TargetDate As Date
    TargetDate = Worksheets("Sheet1").Range("D2").Value
    If TargetDate &gt; Date Then
        MsgBox "Number of Days to Compliance: " &amp; TargetDate - Date, vbInformation
    End If
End Sub

clairemcf · Answer

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?

Forum Discussion

VBA Code to Count Down Days

7 Replies

Resources