Start a daily countdown reminder from certain date

[Ironman]

Hi

The below code, currently commented out in the Workbook_Open event, counts down (i.e. the number of days remaining reduces in each daily msgbox) until Jan 1 to remind me to update some code.

MsgBox "Reminder: Today's Date is " & Date, vbInformation, "New Year Update Countdown" & vbNewLine _
            & "New Year Updates due in " & (Range("VBADaysLeft"))+1 & " days", vbInformation , "Exercise Log"

[The named range "VBADaysLeft" is DATE(YEAR(TODAY()),12,31)-LOOKUP(10^35,A:A) and works perfectly OK]

The problem is, it's commented out because I don't want the above to start running until 25 December each year but I will need reminding the week before the new year each year.

Can this be automated so I start receiving the daily reminder when I open the workbook between 25 December and 1 January, and not just on 25 December? (else I would simply insert the below line)

If Date = DateSerial(Year(Now), 12, 25) Then

Thanks!

 

[Akuini]

I think the tricky part is 1 January, because the Year(Now) will be different.
Why not use the exact year, something like this:

Dim x As Long
If Date >= DateSerial(2021, 12, 25) And Date <= DateSerial(2022, 1, 1) Then
x = DateSerial(2022, 1, 1) - Date
MsgBox x
End If

 

[Ironman]

That looks good Akuini, many thanks for your solution - I just have to hope with my ageing memory that I'll remember to adjust the DateSerial

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.

I just have to hope with my ageing memory that I'll remember to adjust the DateSerial

How about this:

Dim x As Long
 
 If Format(Date, "mm-dd") = "01-01" Then
    MsgBox "0"
 Else
    If Date >= DateSerial(Year(Now), 12, 25) And Date <= DateSerial(Year(Now), 12, 31) Then
        x = DateSerial(Year(Now), 12, 31) + 1 - Date
        MsgBox x
    End If
 End If

 

[Ironman]

Ah, that's great, many thanks again Akuini!

 

[Akuini]

Glad it works.