Macro Notification

LauraMuniak 

Here is another example with the file. It looks in all worksheets and if the date fits into the schema of the requirement in any worksheet in A1.

The file opens in this worksheet and a window appears where it informs you in which sheet the date is close or how many days are left.

Anyway, take a look at the file.

Hope it helps.

LauraMuniak 

If I understand what you want…  Obviously, the "all of them" notifications will occur first, when the workbook is opened.  For the "few of them in one spreadsheet", I presume you want those notifications when a relevant worksheet is activated (typically by clicking on its tab).  So you will get more than 3 notifications per due date, but only on 3 separate days.

This can be accomplished by putting most code into a type of procedure called a subroutine (it is not, strictly speaking, a macro) that accepts a worksheet name as a parameter.  The subroutine will be invoked from two types of places: the Workbook_Open event handler (stored in the ThisWorkbook code module) and the Worksheet_Activate event handler(s) (stored in the code module for each relevant worksheet).

To record the number of days before the due date when the last notification was issued, I reserve cell B1.  Feel free to change that location.  (Assumption: At some time after the notifications appear, the user saves the workbook.  Otherwise, the notifications will appear again next time.)  Here's the subroutine code:

Sub DueDateNotification1(ByVal SheetName As String)

    Dim objWorksheet    As Worksheet
    Dim dteDueDate  As Date
    Dim objNotifiedCell As Range
    Dim in2NotifiedDay  As Integer  'the number of days left when _
            the latest popup notification was made
    Dim in2DaysLeft     As Integer
    
    '----   Create a reference to the appropriate worksheet.
    Set objWorksheet = Sheets(SheetName)
    
    '----   If there's a date in cell A1...
    If IsDate(objWorksheet.Range("A1").Value) Then
        '  --   Capture the due date, and the latest notification
        '       day from its reserved cell.
        dteDueDate = objWorksheet.Range("A1").Value
        Set objNotifiedCell = objWorksheet.Range("B1")
        in2NotifiedDay = objNotifiedCell.Value  '(An empty cell is _
                treated by this assignment as containing zero.)
        
        '  --   Calculate the number of days until the due date.
        in2DaysLeft = DateDiff("d", Date, dteDueDate)
        
        '  --   If the due date is in the future...
        If in2DaysLeft > 0 Then
            '   Show a message on three separate days (assuming the workbook
            '   is opened on sufficient days) before the due date.
            If in2DaysLeft <= 28 And in2DaysLeft > 14 _
            And (in2NotifiedDay = 0 Or in2NotifiedDay = in2DaysLeft) Then
                Call MsgBox("You have " & in2DaysLeft & " days until the due date. (" _
                        & SheetName & ")", vbInformation + vbOKOnly)
                objNotifiedCell.Value = in2DaysLeft
                ' *
            ElseIf in2DaysLeft <= 14 And in2DaysLeft > 7 _
            And (in2NotifiedDay = 0 Or in2NotifiedDay = in2DaysLeft _
                    Or in2NotifiedDay > 14) Then
                Call MsgBox("You have " & in2DaysLeft & " days until the due date. (" _
                        & SheetName & ")", vbInformation + vbOKOnly)
                objNotifiedCell.Value = in2DaysLeft
                ' *
            ElseIf in2DaysLeft <= 7 _
            And (in2NotifiedDay = 0 Or in2NotifiedDay = in2DaysLeft _
                    Or in2NotifiedDay > 7) Then
                Call MsgBox("You have " & in2DaysLeft & " days until the due date. (" _
                        & SheetName & ")", vbInformation + vbOKOnly)
                objNotifiedCell.Value = in2DaysLeft
                ' *
            End If
        End If
    End If
    
    ' * You could force the workbook to be saved at this point.  I chose
    '   not to do that.

End Sub

Of course, if you have multiple due dates per worksheet (Are you sure you want that many notification messages?), the DueDateNotification1 subroutine could be modified to handle that.

To include a check each time the workbook is opened, the Workbook_Open event handler would contain code such as this:

Private Sub Workbook_Open()
'   This procedure is executed automatically when the workbook is opened.

    'Call the procedure(s) to (possibly) display due-date notifications.
    Call DueDateNotification1("Sheet0")
    Call DueDateNotification1("Sheet1")

End Sub

To include an additional check each time a worksheet is activated, the Worksheet_Activate event handler would contain this:

Private Sub Worksheet_Activate()
'   This procedure is executed automatically when the worksheet is activated.

    'Call the procedure to (possibly) display due-date notifications.
    Call DueDateNotification1(Me.Name)

End Sub

Note that you do not need to hard-code the worksheet name.  Each worksheet object (Me, in the context of the worksheet code module) exposes its name as a property.

Note also that you get no notification on the due date itself, even if such a notification is "overdue".