VBA Code for Due Dates on Different Sheets

[Thalita]

Hello all, I am trying to create a VBA Code for cases that are due on the day I open it. The goal is to receive a pop up message every day. I have 3 sheets that I need this code to work, being that each sheet has its due dates on a different column.

Sheet 1 ("R2:R500")
Sheet 2 ("M2:M500")
Sheet 3 ("H2:H500)

I have found a similar article that helped me with this question but I am having a hard time editing the code to reflect different columns for different sheets. Another point is I just need the pop up notifications to show cases that are due on the day off and days that expired. Notifying me of the due dates coming up is not needed. Below is the code I currently have which I have added to the "This Workbook" Tab, I would greatly appreciate someone's help as this will save me and my team tons of manual work.

Thanks in advance!

____________________________

Private Sub Workbook_Open()

Dim cl As Range
Dim rng As Range
Dim str As String
Dim sht_str As String
Dim sht As Worksheet

sht_str = "Attention! The following Reminders are due, or have already expired: " & Chr(10) & Chr(10)

For Each sht In Me.Worksheets
sht_str = sht_str & sht.Name & ":"
str = ""
Set rng = sht.Range("H2:H500")
On Error GoTo exit_sub
For Each cl In rng
If cl.Value = "" Then GoTo Next_cl
If cl.Value < Date + 1 And cl.Value > Date - 1 Then str = str & Chr(10) & "Row " & cl.Row

Next_cl:
Next cl
If str = "" Then str = Chr(10) & "No reminders due on this sheet"
sht_str = sht_str & str & Chr(10) & Chr(10)
Next sht
MsgBox sht_str, 48, "Reminders Due!"
exit_sub:
End Sub

 

[tonyyy]

You might consider the following...

Private Sub Workbook_Open()
Dim cl As Range
Dim rng As Range
Dim str As String
Dim sht_str As String
Dim sht As Worksheet

sht_str = "Attention! The following Reminders are due, or have already expired: " & Chr(10) & Chr(10)

For Each sht In Me.Worksheets
    sht_str = sht_str & sht.Name & ":"
    str = ""
    Select Case sht.Name
        Case "Sheet1": Set rng = sht.Range("R2:R500")
        Case "Sheet2": Set rng = sht.Range("M2:M500")
        Case "Sheet3": Set rng = sht.Range("H2:H500")
    End Select
    On Error GoTo exit_sub
    For Each cl In rng
        If cl.Value <= Date And cl.Value <> "" Then str = str & Chr(10) & "Row " & cl.Row
    Next cl
    If str = "" Then str = Chr(10) & "No reminders due on this sheet"
    sht_str = sht_str & str & Chr(10) & Chr(10)
Next sht
MsgBox sht_str, 48, "Reminders Due!"
exit_sub:
End Sub

Cheers,

Tony

 

[Thalita]

You might consider the following...

Private Sub Workbook_Open()
Dim cl As Range
Dim rng As Range
Dim str As String
Dim sht_str As String
Dim sht As Worksheet

sht_str = "Attention! The following Reminders are due, or have already expired: " & Chr(10) & Chr(10)

For Each sht In Me.Worksheets
    sht_str = sht_str & sht.Name & ":"
    str = ""
    Select Case sht.Name
        Case "Sheet1": Set rng = sht.Range("R2:R500")
        Case "Sheet2": Set rng = sht.Range("M2:M500")
        Case "Sheet3": Set rng = sht.Range("H2:H500")
    End Select
    On Error GoTo exit_sub
    For Each cl In rng
        If cl.Value <= Date And cl.Value <> "" Then str = str & Chr(10) & "Row " & cl.Row
    Next cl
    If str = "" Then str = Chr(10) & "No reminders due on this sheet"
    sht_str = sht_str & str & Chr(10) & Chr(10)
Next sht
MsgBox sht_str, 48, "Reminders Due!"
exit_sub:
End Sub

Cheers,

Tony

Hi Tony, thank you for taking your time to build this VBA code for me, but unfortunately when I input this code and save it nothing happens. No notifications pop up when I open excel, like it was happening before (Please see attached). The only correct sheet is "Sheet 3" Mitigation Control Column H. The image attached is displaying "Sheet 1" Status Column R, where I inputted today's date and it should pop a reminder for that one as well.

Really appreciate your time with this matter.

Thanks!