Excel VBA到期日弹窗提醒优化：如何忽略昨日之前的日期

Hey there, let's get that expired date exclusion working for your Excel reminder! The issue with your original attempt is that resetting NotificationMsg = "" every time you hit a past date would wipe out any valid upcoming dates you'd already added. Instead, we need to skip adding past dates to the notification list entirely.

Here's the revised VBA code that includes the "ignore dates before today" logic:

Sub Workbook_Open()
    Dim DateDueCol As Range
    Dim DateDue As Range
    Dim NotificationMsg As String
    
    Set DateDueCol = Range("B2:B500") ' 存放到期日的单元格范围
    
    For Each DateDue In DateDueCol
        ' 仅处理非空、到期日不早于今天，且在提醒天数范围内的日期
        If DateDue <> "" And DateDue >= Date And Date >= DateDue - Range("O2") Then
            ' Offset(0,0)是到期日本身，若要显示对应事项名称可调整偏移列，比如Offset(0,1)取C列内容
            NotificationMsg = NotificationMsg & vbCrLf & "- " & DateDue.Value
        End If
    Next DateDue
    
    If NotificationMsg = "" Then
        MsgBox "You do not need to complete any SHOPPING, today."
    Else
        MsgBox "The following Shopping need to be completed soon: " & vbCrLf & NotificationMsg
    End If
End Sub

关键改动说明： #

• 新增了DateDue >= Date条件：直接跳过所有昨日及更早的过期日期，不会把它们加入通知消息
• 调整了消息拼接格式：用vbCrLf（换行符）和- 让通知列表更易读，避免日期挤在一起
• 保留了你原有的提醒天数逻辑（Date >= DateDue - Range("O2")），确保只提醒即将到期的事项

额外小提示： #

如果你的到期日列旁边有对应的购物事项名称（比如C列），可以把DateDue.Value改成DateDue.Offset(0,1).Value，这样弹窗里会显示具体事项名称，而不只是日期，实用性更强！

内容的提问来源于stack exchange，提问作者marie joanna