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
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.
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.
I Followed the exact procedure but it did not work. What should I do? I don’t know where I am wrong.
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.
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?
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.
Thanks Its worked
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?
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.
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?
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.
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?
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,
Thank you very much. the code works