Letter Log Reminder with condition in other cells.

[Rayreloj10]

Hello everyone,

I will try to explain again.

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 appears everytime I click the sheets. I

here's my code I create. The code already works, however 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.


As you can see below, the condition is if the the DUEDATE is not empty and it is greater than or equal to the current Date -2. now I need to add one more condition. so then: if the DUEDATE is not empty and it is greater or equal to the current date -2 AND if the Status is "Open".

A	B	C	D	E​
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("2023").Range("L10:L373")
'Set LetterStatus = ThisWorkbook.Sheets("2023").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

 

[Micron]

Just add another logical test to your existing tests. Perhaps

If DateDue <> "" And Date >= DateDue - 2 And DateDue.Offset(0, 1) = "Open" Then

 

[Rayreloj10]

Thank very much! it works!

 

[Rayreloj10]

Hi! again another issue comes up. for some reason the Msgbox is not updating with the new data entry. as shown above, in the current example. the message box will show the letter number "00001" and "00002". however, when you enter new letter number with the same condition, it will not show the new data entry.

 

[Micron]

Then I guess you should be using the sheet change event instead of sheet activate?

 

[Rayreloj10]

Thank you for your reply, I cannot use the sheet_change because the Msgbox will trigger everytime I change anything in the worksheet. I multiple log in the workbook, that is why I need the Msgbox to appear everytime I'm opening every worksheet.

 

[Micron]

I cannot use the sheet_change because the Msgbox will trigger everytime I change anything in the worksheet

Not if the change is outside of the target range you specify.

 

[Rayreloj10]

I'm not exactly sure what was the problem but my code is working perfectly now without changing anycode.

 

[Micron]

Glad I could help, and thanks for the recognition.