Checking date coming up soon?

[Shaun Thornhill]

I have a spreadsheet where in Column M is a list of dates in the future. I would like to add a pop up message, so when I open the workbook, it lets me know if a date is within 30 days away.

Hope that makes sense!

Cheers

[MikeTheBike]

Hi

Not sure what you want to do (ie just count the dates or list then), but with this code in the 'ThisWorkbook' module (and macros enabled!!) it is a start!?

Private Sub Workbook_Open()
    Dim iDateCol As Integer
    Dim iBotRow As Long
    Dim i As Long
    Dim DateCount As Long
    
    iDateCol = 13
    
    
    ThisWorkbook.Sheets("Dates Sheet").Select
    
    ActiveCell.SpecialCells(xlLastCell).Select
    iBotRow = ActiveCell.Row
    Cells(1, 1).Activate
    
    For i = 1 To iBotRow
        If IsDate(Cells(i, iDateCol)) Then
            If Cells(i, iDateCol) <= Date + 30 Then DateCount = DateCount + 1
        End If
    Next i
    
    If DateCount > 0 Then MsgBox "There are " & DateCount & " dates within 30 dates in Coulmn 'M'.", vbInformation, "Due Dates"

End Sub

You will have to change the name of the sheet containing the dates in line 10, just in caes there is more than one sheet.

MTB