Tom’s Tutorials for Excel: Workbook Suicide

Tom’s Tutorials for Excel: Workbook Suicide: Make a workbook delete itself.
Tom Urtis has an intriguingly titled new one today.

If you have developed a workbook which you want to self-expire by a certain date, such as a demonstration model or containing information or usefulness that will be outdated, you can program a workbook to delete itself.

Place the following code in the workbook module, which in this example specifies the workbook’s suicide date of December 31, 2011. In actual practice, you might also want to have a message box appear during, say, the 7 days prior to the suicide date to let the workbook’s users know what to expect on the upcoming date of demise. This is a case where you would also want to lock and password protect the Visual Basic Editor, to reduce the chance for the code to be altered or deleted.

Workbook_Open is a workbook-level procedure, meaning it would be placed in the workbook module of your workbook, usually named ThisWorkbook.

To easily access your workbook module…

In Excel version 2003 or before:
Find the small Excel workbook icon near the upper left corner of your workbook window, usually just to the left of the File menu item. Right-click on that icon, and select View Code.

In Excel version 2007 or after:
From your worksheet press Alt+F11, then press Ctrl+R, find your workbook name in the “Project – VBAProject” left vertical pane, expand the Microsoft Excel Object folder for your workbook, right click on ThisWorkbook and select View Code.

Paste the below procedure into the large white area that is the workbook module. Press Alt+Q to return to the worksheet.

Be aware, this code truly deletes the workbook, even bypassing the Recycle Bin!

Sub Workbook_Open()
If Date <= #12/31/2011# Then Exit Sub
MsgBox "This workbook has expired.", 48, "Goodbye."
With ThisWorkbook
.Saved = True
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
End Sub

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,
6 comments on “Tom’s Tutorials for Excel: Workbook Suicide
  1. Martin says:

    I pasted the above code you described on Sheet1 (Sheet1) at the Microsoft Excel Object.
    But nothing happens even when i re-open the file after saving & close.

    • Tom Urtis says:

      You did not read — or if you did read it, you did not comprehend — the directions I gave to install the code. I did not say to install the code on a sheet or in the module of a sheet. I said the code would be placed in the workbook module, and I gave directions on how to do that from different versions of Excel. If you follow those directions, the code will work.

  2. Umair says:

    I Followed the exact procedure but it did not work. What should I do? I don’t know where I am wrong.

    • Tom Urtis says:

      Did you put the code where I said to put it, in the workbook module. Not in a standard module such as where macros and UDFs go, but in the ThisWorkbook module. Also, did you first save your workbook as a ,xlsm file. The code does work, so try looking at exactly where you placed it in your project.

  3. Kundan says:

    Thanks for the code.
    However, the following steps save the workbook even if date is expired (with code locked with password):
    > click on X in Goodbye dialogue
    > click on Cancel in the warning “Be careful! Parts of your document..” from excel
    > click on End in Run time error 1004

    Any help, please?

    • Tom Urtis says:

      Regarding the X close button, instead of a message box, you can use a userform with the UserForm_QueryClose statement, although with the message box code I did not get the same result you did when clicking that close button; the code still executed. A run time error would not occur under ordinary circumstances. Your second item is only invoked if the user does not activate the programming code depending on their security setting. If that is the case, the workbook by design would be unworkable anyway. Keep in mind you do not even need a message box warning. You can destroy the workbook without a warning. And you can design the workbook so that unless the code is activated, all sheets are veryhiddden except for a useless empty one, and data on all sheets is encrypted in some way.

Leave a Reply

Your email address will not be published. Required fields are marked *

*