You will need to copy this code to ThisWorkbook section of your workbook .
|Private Sub Workbook_Open()|
Dim Edate As Date
Edate = Format("31/08/2009", "DD/MM/YYYY") ' Replace this with the date you want
If Date > Edate Then
MsgBox ("This worksheet was valid upto " & Format(Edate, "dd-mmm-yyyy") & " and will be closed")
If Edate - Date < 30 Then
MsgBox ("This worksheet expires on " & Format(Edate, "dd-mmm-yyyy") & " You have " & Edate - Date & " Days left ")
Once the expiry date is crossed, user will get message and workbook will be closed by macro
Once the expiry date in with next thirty days, user will get warning message about expiry date and number of days left.