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

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.

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!

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

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:

Dim objWorksheet    As Worksheet
Dim dteDueDate  As Date
Dim objNotifiedCell As Range
Dim in2NotifiedDay  As Integer  'the number of days left when _
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.

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.

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