MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Create a Pop-up warning box

Posted by Greg V on February 14, 2002 11:57 AM

I posted this before, but the answer I recieved (using data validation) does not work.

Basically, I want to have a list of documents and the dates that they expire. I want to create some type of warning (preferably a pop up message) to notify the user when a document is 6 months from expiring. Is there a way to do this?

Posted by Ed Acosta on February 14, 2002 3:22 PM

The following code will give you a warning each time the workbook is opened. I'm assuming the data is located in sheet 1 in columns A & B. I will e-mail you the sample spreadsheet so you can see how it works.

Private Sub Workbook_Open()

Dim C, T As Integer
Dim DN, DD As String


For T = 2 To Range("A65536").End(xlUp).Row
DT = Range("B" & T)
If DT < Now() + 180 Then DN = DN & " " & Range("B" & T) & " " & Range("A" & T) & Chr$(13)
Next T

MsgBox ("The Following Documents will expire within 6 months: " & DN)

End Sub