Pop Up Reminder, based off of Dates

[Afox10]

Good afternoon all,

I have an accounting spread sheet that has various purchased machines and data that comes with paying those machines off.

I am looking for a way to have a pop-up alert me when I open the spreadsheet, of when the machines reach their 60 month payment deadline. so for example:

Column B2 has a list of dates:
9/1/2005
4/3/2006
5/1/2007
6/16/2011
1/10/2012
1/1/2013
1/1/2014
9/1/2016

Is there a way to get a pop-up to show up 60 months after each of these dates. As well as any new date I put in that column?

Thank you for any thoughts and considerations with this!

AFox

 

[Villalobos]

Hi,

Check out this approach.

Option Explicit
Private Sub Workbook_Open()
Dim LastRow As Long
Dim a As Long
Dim Message As String
Dim Today As Integer
  
    With Worksheets("Sheet1")
    LastRow = .Range("B" & .Rows.Count).End(xlUp).Row
        For a = 2 To LastRow
            If Worksheets("Sheet1").Range("B" & a) - Date < Today - (365 * 5) Then
                Message = Message & .Range("B" & a) & vbCrLf
            End If
        Next a
    End With
  
    If Len(Message) > 0 Then
        MsgBox "Check these dates due to 60 months deadline:" & vbCrLf & Message, 64, "Reminder for you!"
    End If
  
End Sub

 

[KenU]

Perhaps Conditional Formatting would be sufficient. It is not a pop-up, but could certainly draw the user's attention to the relevant dates.

Set up conditional formatting using a formula like:
=DATE(YEAR(B2)+5,MONTH(B2),DAY(B2))<=TODAY()

Be sure to set up the formatting to something that will make the results stand out.

I hope that helps.

Regards,
Ken

 

[Afox10]

@Villalobos, This seems to work! I did have to adjust some of the referencing names, but it works like a charm! Thank you!

@KenU, Unfortunately the spreadsheet is going to accumulate a couple hundred machines. The pop up route makes it so much faster, HOWEVER, I am going to try this formula in another area of the spreadsheet. Will let you know if I run into any issues, thank you for shareing!