如何在指定单元格输入日期时终止VBA弹出窗口

核心思路是在原有代码开头加一个判断：检查指定的控制单元格是否已输入有效日期，要是有就直接终止程序，不执行后续的弹窗逻辑；没有的话再正常运行原有的到期提醒流程。

修改后的代码如下：

Sub CheckReturnReminders()
    Dim DateDueCol As Range
    Dim DateDue As Range
    Dim NotificationMsg As String
    Dim ControlCell As Range ' 定义控制弹窗触发的单元格
    
    ' 替换成你指定的控制单元格地址，比如Range("A1")
    Set ControlCell = Range("A1")
    
    ' 检查控制单元格是否有有效日期，有则直接退出程序
    If IsDate(ControlCell.Value) Then
        Exit Sub
    End If
    
    Set DateDueCol = Range("o3:o100") 'the range of cells that contain your due dates
    
    For Each DateDue In DateDueCol
        'Change o3 to the cell for bring forward reminder days in your data
        If DateDue <> "" And Date >= DateDue - Range("z3") Then
        'Change the offset value to pick up the invoice number column in your data
            NotificationMsg = NotificationMsg & " " & DateDue.Offset(0, -14)
        End If
    Next DateDue
    
    If NotificationMsg = "" Then
        MsgBox "There are none Expected to Return to Work Today."
    Else: MsgBox "The following are Expected Return to Work in Two Days: " & NotificationMsg
    End If
End Sub

关键修改说明 #

• 新增ControlCell变量，你可以把Range("A1")改成实际需要的特定单元格地址
• 前置判断IsDate(ControlCell.Value)：只要该单元格输入了有效的日期格式，程序就直接退出，不会触发任何弹窗
• 原有的到期提醒逻辑完全保留，仅在控制单元格无有效日期时才会执行

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