Pop up reminder message in excelClosed

Question

Hi Guys, 

I'm just wondering if you would be able to assist me with this one, I have been trying to work this out myself but this is causing me a great deal of stress... 

I am trying to get Excel to generate a popup when it reaches or passes a certain date. 

We have to send out notifications to our residents for any up and coming maintenance events that are scheduled in.

We have 3 dates;
Maintenance Due Date (Column D)
Notification Due Date (Column E)
Notification Sent date (Column F)


I am trying to set it up so that a pop up message appears in the following two circumstances; 

 Popup 1: - When the date from a cell in column E (Notification Due Date) is = to 'today's' date. 

I would like the message to give information of the notification that is due, for instance: 

If Notification Due Date (E3) is = to 'Today' then I would like the pop up to give the following message:

"NOTIFICATIONS ARE DUE TO BE SENT TODAY"

"Location:" & Info from cell A3
"Maintenance Task:" & Info from cell B3
"Notification Due Date:" & info from cell E3

 Popup 2: - when a cell in column F (Notification Sent Date) has been left blank and the date on the same line in column E (Notification Due Date) = or >

In this case I would like the message to give information of the notification that is over due, for instance: 

If Notification Sent Date (F3) is blank and the date in cell E3 (Notification Due Date) is >or = to 'Today' then I would like the pop up to give the following message:

"NOTIFICATIONS ARE OVER DUE"

"Please check and update tracker as the following notifications have been missed:"  

"Location:" & info from cell A3
"Maintenance Task:" & info from cell B3
"Maintenance Due Date:" info from cell D3

any assistance would be greatly appreciated  

Configuration: Windows 7 / Chrome 46.0.2490.86

vcoolio · Accepted Answer

Hello Blane,

Perhaps the follwing codes may help you:-

Sub Advise()

Dim x As Integer
      Dim lRow As Long
lRow = Range("E" & Rows.Count).End(xlUp).Row

For Each cell In Range("E2:E" & lRow)
x = cell.Row
     r1 = Range("A" & x) 'Location
     r2 = Range("B" & x) 'Maintenance Task
     r3 = Range("E" & x) 'Notification Due Date
If cell = [today()] Then
MsgBox "NOTIFICATIONS ARE DUE TO BE SENT TODAY!" & vbNewLine & "Location : " & r1 & vbNewLine & "Maintenance Task: " & r2 & _
 vbNewLine & "Notification Due Date: " & r3, vbExclamation, "WARNING!"
     End If
Next
Advise2
End Sub

Sub Advise2()

Dim I As Integer
      Dim lRow As Long
lRow = Range("E" & Rows.Count).End(xlUp).Row

For I = 2 To lRow

r1 = Cells(i, 1) 'Location
     r2 = Cells(i, 2) 'Maintenance Task
     r3 = Cells(i, 4) 'Maintenance Due Date
     
If Cells(i, 5) >= [today()] And Cells(i, 6) = "" Then

MsgBox "NOTIFICATIONS ARE OVERDUE!" & vbNewLine & "Location : " & r1 & vbNewLine & "Maintenance Task: " & r2 & _
 vbNewLine & "Maintenance Due Date: " & r3, vbExclamation, "WARNING!"
 
End If
Next i

End Sub

Pop-up messages will appear to advise you as per your post (I think!). They will appear one after the other depending on how many cells are affected. So, once you click on OK in the pop-up message box, the next affected cell details will be displayed etc.. The first code calls the second code on completion of its "cycle".

Following is a link to my test work book which will show you how the code works. Just click on the "Warnings!" button. The dates that I have used in the example are just random so change these to suit yourself.

https://www.dropbox.com/s/t22rs8wuwv4cplo/Blane.xlsm?dl=0

I hope that this helps.

Cheerio,
vcoolio.

vcoolio · Answer

Hello Raj,

Your issue is a little different and may only require basic "IF" statements.

Please start a new thread and include a sample of your work book showing exactly what you are trying to achieve. Be careful with any sensitive data.

Upload a sample of your work book to a free file sharing site such as DropBox, ge.tt or SpeedyShare then post the link to your file in your new thread.

Someone should then be able to help you.

Cheerio,
vcoolio.

Pop up reminder message in excel

2 responses

Similar discussions

Subscribe To Our Newsletter!