Macro Notification

Copper Contributor

Hello, 

 

I need to create macro with pop up notification. I want notification to pop up 3 times

28 days till due date

14 days till due date 

7 days till due date 

4 Replies

@LauraMuniak 

Here is an example code with an example file of how it could work.

The macro is automatically activated when the workbook is opened.

This macro will check the value in cell A1 for a due date. If there is a valid date, it calculates the number of days left until that due date. Then it displays notifications at 28, 14, and 7 days before the due date. If the due date is in the past or if there is no valid date in cell A1, no notifications will be displayed.

Sub DueDateNotification()
    Dim DueDate As Date
    
    ' Check if there's a date in cell A1
    If IsDate(Range("A1").Value) Then
        DueDate = Range("A1").Value
        
        ' Calculate the number of days until the due date
        Dim DaysLeft As Integer
        DaysLeft = DateDiff("d", Date, DueDate)
        
        ' Check if the due date is in the future
        If DaysLeft > 0 Then
            ' Notify 28 days before due date
            If DaysLeft <= 28 And DaysLeft > 14 Then
                MsgBox "You have 28 days until the due date.", vbInformation
            End If
            
            ' Notify 14 days before due date
            If DaysLeft <= 14 And DaysLeft > 7 Then
                MsgBox "You have 14 days until the due date.", vbInformation
            End If
            
            ' Notify 7 days before due date
            If DaysLeft <= 7 Then
                MsgBox "You have 7 days until the due date.", vbInformation
            End If
        End If
    End If
End Sub

My answers are voluntary and without guarantee!

 

Hope this will help you.

 

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

 

@NikolinoDE is it possible to do few of them in one spreadsheet and all of them to open on opening of work book 

@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".

@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.