Letters Log Due Date Reminder

[Rayreloj10]

Hello everyone,

I'm trying to make a reminder for my letter log and I have more than 1 condition before it includes the letter number in the Msg Box. I have multiple sheet so I need to make the Msgbox appear everytime I click the sheets. I

here's my code I create. The code already works, but its displaying all the letter number in the cell. Msgbox should only display the letter number if it shows the value in the other cell as "OPEN".

really appriciate your help. Thank you in advance.

Letter Number​	Title​	Issued Date​	Due Date​	Status​
00001	Letter1	26/Jun/23	11/July/23	Open
00002	Letter2	25/Jun/23	10/july/23	Closed

Option Explicit

Private Sub Worksheet_Activate()

Dim DateDueCol As Range
Dim DateDue As Range
Dim NotificationMsg As String
Dim LetterStatus As Range
Dim Status As Range


Set DateDueCol = ActiveWorkbook.Sheets("2021").Range("L10:L373")
'Set LetterStatus = ThisWorkbook.Sheets("2021").Range("N10:N373")


For Each DateDue In DateDueCol

If DateDue <> "" And Date >= DateDue - 2 Then
NotificationMsg = NotificationMsg & " " & DateDue.Offset(0, -11)
'ActiveWorkbook.Sheets("Register").Range("A10")

End If


Next DateDue

If NotificationMsg = "" Then

MsgBox "We don't have any pending letter response."


Else: MsgBox "Reminder: We have a pending letters." & vbCrLf & vbCrLf & vbCrLf & NotificationMsg
' ActiveWorkbook.Sheets("Register").Activate



End If
End Sub

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of a couple of sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Rayreloj10]

Thank your advise! however, my computer is a company computer and it has limited permission. I'm just trying not to get any violation in uploading the file.