Saturday, July 18, 2009

Excel Macros : VBA Expiry date for Excel Workbook

Many times we have a situation where we need to force users to use updated file and stop using the old version of worksheet. The only way to do so is to put expiry date for your workbook. User will not be able to work upon the file once the expiry date is crossed and will be forced to look for updated version.

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")
ActiveWorkbook.Close
End If
If Edate - Date < 30 Then
MsgBox ("This worksheet expires on " & Format(Edate, "dd-mmm-yyyy") & " You have " & Edate - Date & " Days left ")
End If
End Sub

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.

Download file containing Workbook Expiry Date Macro

8 comments:

  1. how do i convert numbers in words in xcel format??? m findin it very difficult...

    ReplyDelete
  2. can this run automaticaly once the user trys to open the file or do i have to run the macro?

    ReplyDelete
  3. can this run automaticaly once the user trys to open the file or do i have to run the macro?

    ReplyDelete
  4. Would the author be able to get back into an expried file, since once it expires it will not open?

    ReplyDelete
  5. i want to send an excel file that gets deleted after a week, i dont have a VB editor. Is it possible to add the code and which code should i need to add and what will be the steps for the same. Thanks

    ReplyDelete
  6. Great! This is exactly what I needed. Thanks.

    ReplyDelete
  7. I want notification alert for date of expiry for medicine. I already created a vb application for product entry with date of expiry column. can you pls tell me how can i add code to my existing userform to get notification 30 prior for near expiry goods.

    ReplyDelete
  8. Ooh, this is something new as I have never experience or heard about the excel workbook expiring. But now, I will take care of this issue in the future and use the updated version to afford any inconvenience. Like the way I pay to do my coursework online in a professional, I will use professional software to make life easygoing.

    ReplyDelete