Hello,
I apologize if there is a post about this, but I haven't been able to find a fix for my particular situation. I am working on a spreadsheet that can track some reagents and alert at opening with message boxes about 3 options: 1) No expired reagents, 2) Reagents about to expire within the next 30 days (Msgbox should display what reagent is about to expire), 3) Expired reagents (Msgbox should display what reagents have expired).
Relevant columns are A (Reagent name) and D (Expiration date).
This is the code I have so far. Unfortunately it only shows expired reagents and no expired reagents. I don't know how to add the third option for about to expire in 30 days and make it work. Many Thanks!!!!!
Private Sub Workbook_Open()
Dim ExpirationDateCol As Range
Dim ExpirationDate As Range
Dim NotificationMsg As String
Set ExpirationDateCol = Range("D2:D10000")
For Each ExpirationDate In ExpirationDateCol
If ExpirationDate <> "" And Date >= ExpirationDate Then
NotificationMsg = NotificationMsg & " " & ExpirationDate.Offset(0, -3)
End If
Next ExpirationDate
If NotificationMsg = "" Then
MsgBox "There are no expired reagents."
Else: MsgBox "The followinging reagents have EXPIRED (Remove from circulation): " & NotificationMsg
End If
End Sub
I apologize if there is a post about this, but I haven't been able to find a fix for my particular situation. I am working on a spreadsheet that can track some reagents and alert at opening with message boxes about 3 options: 1) No expired reagents, 2) Reagents about to expire within the next 30 days (Msgbox should display what reagent is about to expire), 3) Expired reagents (Msgbox should display what reagents have expired).
Relevant columns are A (Reagent name) and D (Expiration date).
This is the code I have so far. Unfortunately it only shows expired reagents and no expired reagents. I don't know how to add the third option for about to expire in 30 days and make it work. Many Thanks!!!!!
Private Sub Workbook_Open()
Dim ExpirationDateCol As Range
Dim ExpirationDate As Range
Dim NotificationMsg As String
Set ExpirationDateCol = Range("D2:D10000")
For Each ExpirationDate In ExpirationDateCol
If ExpirationDate <> "" And Date >= ExpirationDate Then
NotificationMsg = NotificationMsg & " " & ExpirationDate.Offset(0, -3)
End If
Next ExpirationDate
If NotificationMsg = "" Then
MsgBox "There are no expired reagents."
Else: MsgBox "The followinging reagents have EXPIRED (Remove from circulation): " & NotificationMsg
End If
End Sub