Letter Log Reminder MsgBox not updating

[Rayreloj10]

Hi!

new issue came up, previous issue is resolved. However, the Msgbox only display the current data in the cell. if new data is entered in the worksheet, it will still show the previous msg. like for example as shown below. The msgbox will show that reminds your that the letter number 00001 is open. but when you add letter number 00003, msgbox will not show the new data entry 00003.

in addition, when you change the status of existing data into "Closed", such as letter number 00001, it will be deleted in the message box but when you change it back in "Open" status will be not appear again. I already modify the Range to make sure that it covers the new data entry.

Thank you for the assistance in advance.

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
00003	Letter3	07/july/23	10/july/23	Open

Option Explicit

Private Sub Worksheet_Activate()

Dim DateDueCol As Range
Dim DateDue As Range
Dim NotificationMsg As String

Set DateDueCol = ActiveWorkbook.Sheets("2023").Range("L10:L373")

For Each DateDue In DateDueCol

If DateDue <> "" And Date >= DateDue - 2 and  DateDue.Offset(0, 1) = "Open"Then
NotificationMsg = NotificationMsg & " " & DateDue.Offset(0, -11)
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
End If
End Sub

 

[rlv01]

I do not see either of those problems when I run your code. Keep in mind that you are using the Worksheet_Activate event to run your code. It only runs when you first activate the worksheet, not when you make changes like adding a line or changing a status value from Open to Closed. To see the changes you have to leave the worksheet and come back so that the Worksheet_Activate event runs again.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Letter Number	Title	Issued Date									Due Date	Status
2
3
4
5
6
7
8
9
10	1	Letter1	26-Jun-23									11-Jul-23	Open
11	2	Letter2	25-Jun-23									10-Jul-23	Open
12	3	Letter3	7-Jul-23									10-Jul-23	Closed
13	4	Letter3	7-Jul-23									10-Jul-23	Open
2023

 

[Rayreloj10]

I do not see either of those problems when I run your code. Keep in mind that you are using the Worksheet_Activate event to run your code. It only runs when you first activate the worksheet, not when you make changes like adding a line or changing a status value from Open to Closed. To see the changes you have to leave the worksheet and come back so that the Worksheet_Activate event runs again.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Letter Number	Title	Issued Date									Due Date	Status
2
3
4
5
6
7
8
9
10	1	Letter1	26-Jun-23									11-Jul-23	Open
11	2	Letter2	25-Jun-23									10-Jul-23	Open
12	3	Letter3	7-Jul-23									10-Jul-23	Closed
13	4	Letter3	7-Jul-23									10-Jul-23	Open
2023

That's why I'm confused yesterday, I run different test to check what is the problem. I even closed the excel but it shows the same result. It might be some kind of bug in excel, right now it working perfectly, eventhough I did not change anything in the code.