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: , , , , , , , , , , , ,
23 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.

  4. Dipak Singh says:

    Thanks Its worked

  5. P K MAHENDRA says:

    Sir,

    I pasted your code in Excel 2007 in code of Thisworkbook. However, if I am opening the workbook with Shift Key Pressed, this code is bypassed and work book works as usual. It doesn’t get deleted at all. How to prevent this?

    • Tom Urtis says:

      You are right, the Shift key can bypass that Open event.
      You could utilize a different event so it executes only once, such as a Worksheet Change event, or some other common event that whoever opens the workbook will eventually do. Also, sometimes the workbook goes into Design mode when the Shift key bypasses the Open event, and there are ways around that such as I have posted previously to handle that.

  6. Doug says:

    Code works great! I was wondering if it can be set to execute after a certain number of times it was open? Or 1 day after it was opened for the first time?

    • Tom Urtis says:

      Answers to both options are yes.

      For the number of times it was open, on a veryhidden worksheet enter a 0 in cell A1.
      In the workbook event, add 1 to that number.
      When the number equals whatever max number you have in mind, replace the code in the macro that looks at the date to be such that if the number now is greater than the max number you have in mind, execute the suicide.

      Basically the same thing for day after first open, just record the date of first open and then at each Open test the today date and if it is greater than the first open date, execute the suicide.

  7. George says:

    Pasted code in workbook module as instructed, saved as macro enabled workbook, and get a runtime error when opening. “Run-time error ’53’: File Not Found”. Debugger highlights the “Kill .FullName” line. Any ideas why this isn’t working?

    • Tom Urtis says:

      Hard to say without seeing everything. The error would have happened at the .Saved = True line if the workbook really was not found.

      Could be an error related to a network drive or some location where the instance of Excel you are using is not able to see the folder location. Could be you are using a Mac not a PC.

      Try appending the code with a message box immediately before the Kill line, with
      MsgBox activeworkbook.fullname
      and see what that message box says.

      Remember to keep a copy of that workbook somewhere else because if/when the code does work, which it always has for me, the workbook will be gone forever,

  8. Wapes says:

    Thank you very much. the code works

  9. Michael Gray says:

    Hi – In order to get the macro to run when opening, I deactivated macro security in developer mode.

    When I opened the spreadsheet, the message “This workbook has expired” came up.

    I also got a “runtime error ‘424’; object required” diagnostic.

    When I press the Debug button, “.Saved = True” is highlighted.

    How can I fix this so the file will auto-destruct?

    Thanks!

    • Tom Urtis says:

      Just as a start for troubleshooting purposes, did you change the expiration date in the code, and if so, to what date.

      Also please say your Excel version or release, and confirm if you are running this workbook on a mac or pc.

      I know the code works, so something else is going on somewhere.

      • Michael Gray says:

        I looked again and discovered I misspelled a word in the sequence. It’s working now.

        Would you please give a modification for the file to auto-destruct 30 days after it’s first opened?

        Thanks!

        • Tom Urtis says:

          Think that through a bit. “after it’s first opened” probably won’t mean when you first save that workbook after you create it. And you would be opening that workbook a few times during the course of its design and programming development.

          What you probably mean is, after it’s first opened by the end user.

          But who is the end user? Anyone who opens the workbook if it’s shared? Does that date start when the first person opened it including you, or besides you, or in addition to anyone who would open it? And how do you want the identification of persons who open the workbook to depend on? The user name? What if the same user opens the workbook on their home computer and then on their business computer? Which date matters as the first date? Than that gets to identifying the computer’s processor ID which is possible and I have posted examples of how to do that.

          So you see, there is more to consider about your question and exactly how to manage it beyond just saying “after it’s first opened.”.

          • Michael Gray says:

            I’m going to send an evaluation copy of the spreadsheet.

            I would keep the development copy of the spreadsheet separate and add the “auto-destruct” macro for an evaluation copy before sending it.

            It would be handy to avoid having to specify an auto-destruct date for every person that I send it to.

            I was thinking the auto-destruct would apply for the copy of the spreadsheet that anyone accessed.

            Maybe that’s not practical?

            • Tom Urtis says:

              I guess it can be deemed practical if you go about it relatively painlessly, with this suggestion for example.

              In this or whatever workbook(s) you are about to distribute, add another sheet, name it SuicideDate, and VeryHide that sheet. Maybe that is a one-time action if this workbook you are talking about is a one size fit all template.

              Before you send out the workbook, open it, go to your Immediate window and type Sheets(“SuicideDate”).Range(“A1”).Value = VBA.Date and press Enter.

              And before all that, edit this line in the Workbook_Open event:
              From
              If Date <= #12/31/2011# Then Exit Sub to If Date <= Sheets("SuicideDate").Range("A1").Value Then Exit Sub This is just an idea but should work.

  10. Michael Gray says:

    Thanks for your reply.

    I was hoping to develop an approach that wouldn’t require opening and making an entry in the worksheet before sending it.

    Otherwise, it seems to me to be more straightforward to simply change the date in the macro before sending it instead of having a separate sheet as you suggest.

  11. Michael Gray says:

    Hopefully your readers are better with Excel than I am.

    The spreadsheet has to be in “macro-enabled” mode. To get in that mode, use the “File”, “save as”, save as file type “Macro-enabled workbook” (*.xlsm).

    The spreadsheet also has to be “trusted”. When you open the spreadsheet the first time after saving and closing it, for the alert SECURITY WARNING Macros have been disabled message, press the Enable Content button and save the spreadsheet. When you open it the next time, it should be “trusted” and the macro should automatically execute without having the press the Enable Content button. I’m not sure if this “sticks” when the file is opened on another computer. I’ll have to test it.

  12. Michael Gray says:

    When I copied a “trusted” spreadsheet from one computer to another computer, the Enable Content button reappeared on the second computer. “Trusted” status doesn’t “stick”. If you send an evaluation copy, whoever receives it will have to select Enable Content for auto-destruct to work. I’m afraid it won’t be useful for that situation. (The purpose for not preserving “trusted” status is to protect the receiving computer from malicious macros.)

Leave a Reply

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

*