This is not a story, an amazing VBA code to protect your highly confidential excel file from theft/miss use.
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.
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.
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.
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.
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
0 Comments