VBA - Pop alert for list of dates

[PT_ANCF]

Hello all,


First of all, I tried to find a similar thread but without much success (as I'm new) for my question. So sorry, if there is already a similar thread and if yes and you have it, just point me to the right direction. Thank you.


So I have a list of client contracts and I would like to have a pop-alert running through VBA that would give me an alert. For example:

In this case, I would like for a pop up tab to appear with the clients that, in column E, are -3 months from the End date (column D) with a message like "Renewal meeting with Client(s) x and y" e.g.


I have the idea that it's possible as I've seen it for one single cell but I would like the alert to catch all the contracts that are -3months. Is this possible?


Thank you so much for the aid.


Best regards,
PT_ANCF

 

[PT_ANCF]

But how are you going to put a checkbox into a cell.

To put a check Mark not Checkbox

You would have to use a special font like maybe Weddings and then enter the character "a" to get a check mark

You can use developer for a check box but

Why not something simple like put "I know" in column I

is enough. can even be an "x".

 

[My Aswer Is This]

After looking at this more and more.

If you enter the dates in column D and E and run the script the popup will appear.

But when you run the same script tomorrow the same ones will popup unless you have a x in column I
Not sure why you did not say x to begin with instead of tick then checkbox

So are you really sure you do not want to compare todays date versus date in column D and E

This script is in no way looking to see what todays date is.

And these dates in the cells only change when you change them.

If I have a appointment on Jan 5 and I want to be notified 30 days in advance I would want the script to look at Jan 5 and see if its within 30 days of today.

 

[PT_ANCF]

After looking at this more and more.

If you enter the dates in column D and E and run the script the popup will appear.

But when you run the same script tomorrow the same ones will popup unless you have a x in column I

That's it. I might be conceptualizing a bit ahead and not expressing myself properly but the idea (I think) it's quite simple so you can go with what you said.

Column D is a manual input I do for each client's end date. Column E at the moment I'm running with EDATE($F;-3)

If I have a appointment on Jan 5 and I want to be notified 30 days in advance I would want the script to look at Jan 5 and see if its within 30 days of today.

For my case, a client's contract ends in December 31 and I want to be notified 90 days in advance. So I want the script to look at December 31 and see if its within 90 days of today.


For the notification, if more than one client ends in December 31 and its within 90 days of today I would like just one notification with all the clients to appear instead of multiple notifications for each client.

Was I more clear?

Thank you

 

[My Aswer Is This]

Try this:
For now this will not exclude x in column I

See if this does what you want.
Will look at dates in column D starting in row(2)
If this does what you want I will then add if column I equals X do not show in Message Box

Sub Date_Differents()
'Modified  3/20/2019  7:40:25 AM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim n As String
Dim nn As String
Lastrow = Cells(Rows.Count, "D").End(xlUp).Row
For i = Lastrow To 2 Step -1
    If Cells(i, "D") < DateAdd("d", -90, Date) Then
        n = vbNewLine & Cells(i, 1).Value & n
    End If
Next
nn = "Renewal alert, clients:"
MsgBox nn & vbNewLine & n
Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

My previous post may not work properly.

I think I have before or after backwards

 

[PT_ANCF]

It's just reading the Google (swapped my clients from colours to the forbes brands) as a renewal.

 

[PT_ANCF]

It's just reading the Google (swapped my clients from colours to the forbes brands) as a renewal.

 

[My Aswer Is This]

I know my first post was wrong.

Try this:

Sub Date_Differents()
'Modified  3/20/2019  9:01:07 AM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim n As String
Dim nn As String
Lastrow = Cells(Rows.Count, "D").End(xlUp).Row
For i = Lastrow To 2 Step -1
    
       If Cells(i, 4).Value <= Date + 90 Then
        n = vbNewLine & Cells(i, 1).Value & n
    End If
Next
nn = "Renewal alert, clients:"
If n <> "" Then
MsgBox nn & vbNewLine & n
Else
MsgBox "None found"
End If

Application.ScreenUpdating = True
End Sub

 

[PT_ANCF]

Seems to be working fine!

 

[My Aswer Is This]

So now if you want to exclude any row that has a X in column I

Try this:
You must have a Upper Case X in column I to have it excluded.

Sub Date_Differents()
'Modified  3/20/2019  10:04:29 AM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim n As String
Dim nn As String
Lastrow = Cells(Rows.Count, "D").End(xlUp).Row
For i = Lastrow To 2 Step -1
    
       
        If Cells(i, 4).Value <= Date + 90 And Cells(i, "I").Value <> "X" Then
            n = vbNewLine & Cells(i, 1).Value & n
    End If
Next
nn = "Renewal alert, clients:"
If n <> "" Then
MsgBox nn & vbNewLine & n
Else
MsgBox "None found"
End If

Application.ScreenUpdating = True
End Sub