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