Smart questions
Smart answers
Smart people
Join Eng-Tips Forums
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Eng-Tips
*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

GregLamberson (Petroleum) (OP)
21 May 08 11:10
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: www.oil-gas-consulting.com
 

psafety (Specifier/Regulator)
21 May 08 11:38
Sub Auto_Open()
 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 (Mechanical)
21 May 08 12:29
Which can be disabled by simply setting macro security to High.
psafety (Specifier/Regulator)
21 May 08 12:36
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 (Chemical)
23 May 08 16:44
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 (Mechanical)
26 May 08 21:41
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 (Structural)
28 May 08 18:24
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 (Structural)
4 Jun 08 10:45
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 (Structural)
4 Jun 08 19:32
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.)
 

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close