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
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.
Hi, "Pop up reminder message in excel" code is awesome, but I want to use this for birthday reminder. Issue is birthdays have different years. I want only the day and month to be checked and given reminder regardless of the year. Please help me.
Thanks in Advance
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.
Thanks in Advance