Expiration Date for Spreadsheet

[GregLamberson]

All:

I have some spreadsheets I have created and want to protect. I have several people who want to use them under a licensing agreement. I would like to add an expiration date to the spreadsheets so that after a certain date, the spreadsheet no longer functions. Is there an easy way to accomplish (I know "easy" is relative, I am a novice+ Excel user)?

Thanks.

Greg Lamberson, BS, MBA
Consultant - Upstream Energy
Website:

http://www.oil-gas-consulting.com

 

[psafety]

Sub Autpen()
ActiveWorkbook.PrecisionAsDisplayed = False
If Date < #6/15/2008# Then Exit Sub
ByeMessage
With ThisWorkbook
.Saved = True
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
End Sub
Sub ByeMessage()
'Self-Destruct Final Message no response needed, just OK
ln1 = "This spreadsheet has a defined expiration date which has expired. Upon"
ln2 = "acknowledgement this file will be deleted. Contact the author for another copy."
ln3 = ""
ln4 = " email - home: XXXXXXX@XXXX.com / work: XXXXXXXXXXXXX@XXXX.com"
ln5 = " Tel: XXX.XXX.XXXX"
msg = ln1 & vbCr & ln2 & vbCr & ln3 & vbCr & ln4 & vbCr & ln5
style = vbOKOnly + vbInformation
Title = " <<< XXXXXXX© January 200X, by Me >>>"
MsgBox msg, style, Title
End Sub

 

[MintJulep]

Which can be disabled by simply setting macro security to High.

 

[psafety]

True Mint Julep, but if you also place some of the most important calculations in VBA, you're most assured it'll be used with macros enalbed.

 

[djack77494]

Besides which, the macro will have the desired effect for 99% of all users. That last 1% could probably figure out how to defeat the macro anyway. At some point, you reach the point of diminishing returns.

 

[brengine]

You can also Password protect the VBA Code, to even more slim down the number of Users able to defeat the Security. It makes it impossible for them to even read the code.

Ken

 

[COEngineeer]

I would put conditional formating that will make the cells all black if the NOW() function is pass certain date. And maybe put a red statement saying it expires. Make sure you put a password so people cant edit it.

Never, but never question engineer's judgement

 

[NormPeterson]

Presumably there's a way to re-save the sheet in disabled or otherwise unusable form so that it can't be re-opened with functionality restored by getting the PC to lie to the sheet about what the date really is . . . simultaneously with or immediately prior to the display of the expiration message might be an appropriate time to do that.


Norm

 

[Denial]

I have implemented expiry dates in some of my spreadsheets. My approach to NormPeterson's worry (that users - bless their little hearts - might crank their system clocks back) has been to have the spreadsheet keep track of the latest date on which it has been run.

This is stored, in encrypted form to make things a little harder for misbehavers, in the Registry. It allows me to detect reverse time travel, and treat it appropriately. (The VBA operations for this use GetSetting and SaveSetting.)