VBA code for pop-up messages to notify due dates

[charlie2503]

Hi everyone,

I am new to VBA and am desperate for some help.
I am working on an excel file containing a list of food product certifications. I have multiple columns (L, N, and X) listing the due dates for 3 different certifications.
Certifications 1 and 3 (in columns L and X) need an alert that is 1 year earlier than the listed due date, while certification 2 only needs an alert 6 months earlier.

I have copied a code that creates a pop-up for certification 1 successfully, but how do I apply this for certifications 2 and 3 with due dates listed in different columns?
Also, is it possible use this code if the file has multiple tabs (considering each tab has the 3 different due dates listed in columns L, N, and X as well).

I'm really struggling with this and would appreciate your help.

Here's the code that I use:

Private Sub Workbook_Open()

Dim RegNumberValidUntilCol As Range
Dim RegNumberValidUntil As Range
Dim NotificationMsg As String

Set RegNumberValidUntilCol = Range("L2:L19")

For Each RegNumberValidUntil In RegNumberValidUntilCol

If RegNumberValidUntil <> "" And RegNumberValidUntil - Date <= 365 Then
NotificationMsg = NotificationMsg & " " & RegNumberValidUntil.Offset(0, -9)
End If

Next RegNumberValidUntil

If NotificationMsg = "" Then

MsgBox "You do not have any product registrations expiring soon."

Else: MsgBox "Registrations of the following products are expiring soon: " & NotificationMsg

End If


End Sub

 

[charlie2503]

try changing this line

 NoDays = RegNumberValidUntil - Date

for this

 NoDays = IIf(i < 3, RegNumberValidUntil - Date, Date - RegNumberValidUntil)

and see if resolves your issue

Dave

It works exactly as I needed. Thank you so much Dave!

 

[dmt32]

It works exactly as I needed. Thank you so much Dave!

Glad resolved & appreciate your feedback

Dave

 

[charlie2503]

Glad resolved & appreciate your feedback

Dave

Hi Dave, I have a quick question. I realized that the pop-up does not show all of the products with upcoming deadlines since the product list is very long. Is there a way to make the pop-up scrollable, so I can see all the listed products by scrolling down?

 

[dmt32]

Hi Dave, I have a quick question. I realized that the pop-up does not show all of the products with upcoming deadlines since the product list is very long. Is there a way to make the pop-up scrollable, so I can see all the listed products by scrolling down?

Not as far as I am aware using the MsgBox function - you probably would need to be creative with a custom userform function to replace it.

Dave

 

[charlie2503]

Not as far as I am aware using the MsgBox function - you probably would need to be creative with a custom userform function to replace it.

Dave

Ah, I see. Thanks for the insight!

 

[charlie2503]

Hi Dave,

This time I need to list product names from 2 columns. Is it possible to include another offset (i, 1, -2) in the formula to refer to another column? I also wonder if it's possible to make the pop-up display into 2 columns instead of 1 long list downwards? I'm so sorry for asking a lot of questions.

This is the code that I am currently using:

[/Private Sub Workbook_Open()

Dim RegNumberValidUntilCol As Range, RegNumberValidUntil As Range
Dim NotificationMsg As String, Prompts As String
Dim i As Long, a As Long, NoDays As Long
Dim ws As Worksheet

Prompts = "You do not have any Halal certificates expiring soon.," & _
"Halal certification of the following products are expiring soon:"

For Each ws In ThisWorkbook.Worksheets(Array("Sheet1"))

Set RegNumberValidUntilCol = ws.Range("M3:M3000,P3:P3000")

For i = 1 To RegNumberValidUntilCol.Areas.Count
For Each RegNumberValidUntil In RegNumberValidUntilCol.Areas(i)

If IsDate(RegNumberValidUntil) Then
NoDays = IIf(i < 3, RegNumberValidUntil - Date, Date - RegNumberValidUntil)
If NoDays >= IIf(i < 3, 0, 240) And NoDays <= 365 Then
If a = 0 Then NotificationMsg = NotificationMsg & Chr(10) & _
ws.Name & Chr(10): a = 1
NotificationMsg = NotificationMsg & _
RegNumberValidUntil.Offset(0, Choose(i, -3, -6)) & Chr(10)
End If
End If

Next RegNumberValidUntil
Next i
Set RegNumberValidUntilCol = Nothing
a = 0
Next ws

MsgBox Split(Prompts, ",")(IIf(Len(NotificationMsg) = 0, 0, 1)) & Chr(10) & _
NotificationMsg, 64, "Notifications"

End Sub]

 

[charlie2503]

Not as far as I am aware using the MsgBox function - you probably would need to be creative with a custom userform function to replace it.

Dave

Hi Dave,

This time I need to list product names from 2 columns. Is it possible to include another offset (i, 1, -2) in the formula to refer to another column? I also wonder if it's possible to make the pop-up display into 2 columns instead of 1 long list downwards? I'm so sorry for asking a lot of questions.

This is the code that I am currently using:

[/
/Private Sub Workbook_Open()

Dim RegNumberValidUntilCol As Range, RegNumberValidUntil As Range
Dim NotificationMsg As String, Prompts As String
Dim i As Long, a As Long, NoDays As Long
Dim ws As Worksheet

Prompts = "You do not have any Halal certificates expiring soon.," & _
"Halal certification of the following products are expiring soon:"

For Each ws In ThisWorkbook.Worksheets(Array("Sheet1"))

Set RegNumberValidUntilCol = ws.Range("M3:M3000,P3:P3000")

For i = 1 To RegNumberValidUntilCol.Areas.Count
For Each RegNumberValidUntil In RegNumberValidUntilCol.Areas(i)

If IsDate(RegNumberValidUntil) Then
NoDays = IIf(i < 3, RegNumberValidUntil - Date, Date - RegNumberValidUntil)
If NoDays >= IIf(i < 3, 0, 240) And NoDays <= 365 Then
If a = 0 Then NotificationMsg = NotificationMsg & Chr(10) & _
ws.Name & Chr(10): a = 1
NotificationMsg = NotificationMsg & _
RegNumberValidUntil.Offset(0, Choose(i, -3, -6)) & Chr(10)
End If
End If

Next RegNumberValidUntil
Next i
Set RegNumberValidUntilCol = Nothing
a = 0
Next ws

MsgBox Split(Prompts, ",")(IIf(Len(NotificationMsg) = 0, 0, 1)) & Chr(10) & _
NotificationMsg, 64, "Notifications"

End Sub]
]