VBA - Pop alert for list of dates

PT_ANCF

New Member
Joined
Mar 18, 2019
Messages
13
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,441
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.
 
Last edited:

PT_ANCF

New Member
Joined
Mar 18, 2019
Messages
13
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,441
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
Code:
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,441
My previous post may not work properly.

I think I have before or after backwards
 

PT_ANCF

New Member
Joined
Mar 18, 2019
Messages
13




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

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,441
I know my first post was wrong.

Try this:

Code:
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
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,441
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.

Code:
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
 

Forum statistics

Threads
1,085,719
Messages
5,385,434
Members
401,951
Latest member
cryptolock

Some videos you may like

This Week's Hot Topics

Top