I have an excel workbook with about 20 different sheets, and I want to make an expiry date pop-up notification based on sheet 12 (Shelf Life Tracking) but it keeps saying Run-time error '13', probably because the code does not specify which sheet it should apply to. Below is the code! How do I specify that I want this macro to run on sheet12 (Shelf Life Tracking)?
Code
Option Explicit
Private Sub Workbook_Open()
Dim DateDueCol As Range
Dim DateDue As Range
Dim NotificationMsg As String
Set DateDueCol = Range("F7:F100") 'the range of cells that contain your due dates
For Each DateDue In DateDueCol
'Change H2 to the cell for bring forward reminder days in your data
If DateDue <> "" And Date >= DateDue - Range("N13") Then
'Change the offset value to pick up the invoice number column in your data
NotificationMsg = NotificationMsg & " " & DateDue.Offset(0, -4)
End If
Next DateDue
If NotificationMsg = "" Then
MsgBox "No items are currently expiring soon."
Else: MsgBox "The following items are expiring in 1 month: " & NotificationMsg
End If
End Sub
Display More