Here is a write-up I have on this subject. Hope it helps.
AFAIK, nothing can be 100% secure in Excel. Any workbooks, worksheets, VB
code, etc can be accessed even if password protected.
Nonetheless, you can password protect the VB code (select the relevant
project in VB, right
click, select Properties and then select Protection. This will act as a
deterrent for most
people who may be interested in viewing the code.
With the code protected you could insert a routine to kick in, say, when the
workbook is
opened or closed such that if the current date is beyond a date you specify,
then most of
the worksheets get deleted, etc, etc. If you need more detail about this
please post back,
but one possibility may be as shown below. Remember that if a user does not
elect to
enable macros when the workbook is opened then the code will not kick in.
This macro adds
a blank sheet and deletes all the other sheets. The blank sheet is
necessary because an
error will be produced if you try to delete the last sheet in a workbook.
Private Sub Workbook_Open()
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False
Dim m As Date, ws As Worksheet
m = DateSerial(2002, 1, 31)
If Date > m Then
Worksheets.Add.Name = "XXX"
For Each ws In Worksheets
If Not ws.Name = "XXX" Then ws.Delete
Next ws
End If
ThisWorkbook.Save
ThisWorkbook.Save
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True
End Sub
Another macro:
Private Sub Workbook_Open()
If Date < DateValue("7/1/03") Then Exit Sub
Sheets("Destroyed").Visible = True
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Destroyed" Then
Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True
End If
Next
ThisWorkbook.Save
ThisWorkbook.Saved = True
End Sub
Another idea that appears to have more merit is as follows:
In the code within the workbook module have a workbook_close event in which
you hide all sheets
except one sheet. This one sheet may be a useless/dummy one.
Then in the workbook_open event use an if statement to check the date.
If the If test for date is true then unhide all sheets and hide the dummy
sheet.
Exit out of the If statement.
This way only if the macros are enabled and only if the date is within the
desired/target one would
you be able to work on the file
AFAIK expiry date checks might be useless because they can be breached by
changing the
computer Time
The If statement would look like:
If Date > #9/15/2004# Then
Note that the # marks force a date.
HTH Otto
