×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • 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!
  • Students Click Here

*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.

Students Click Here

Jobs

Pre-set Termination Date for Spreadsheet

Pre-set Termination Date for Spreadsheet

Pre-set Termination Date for Spreadsheet

(OP)
Can anyone give me insight as to how to set up a spreadsheet with a termination date, or in other words a date after which the spreadsheet calculations will cease to function?

Greg Lamberson, BS, MBA
Consultant - Upstream Energy
Website: www.oil-gas-consulting.com

RE: Pre-set Termination Date for Spreadsheet

In the code for ThisWorkbook (VBA editor), you could add something like:

CODE

Private Sub Workbook_Open()
Dim wks As Worksheet
    If Date > DateSerial(2007, 5, 24) Then
        MsgBox "Sorry, spreadsheet expired..."
        For Each wks In Me.Worksheets
            wks.Range("A1", wks.UsedRange).Copy
            wks.Range("A1").PasteSpecial xlPasteValues
            wks.Range("A1").Select
        Next wks
        Application.CutCopyMode = False
    End If
End Sub
and then password protect the VBA project.
However, if the user holds the Shift key while opening the file, the code doesn't get executed and the spreadsheet still works.
Alternatively, you could put all your proprietary calcs inside VBA procedures and check the date (or some flag) over there. There has been a lot of discussion in this forum already about protection strategies, so I won't repeat that here.

Cheers,
Joerd

Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.

RE: Pre-set Termination Date for Spreadsheet

I like the simplicity of joerd's solution.  Seems to me that only very savvy user's would know to hold shift while opening.  

If one proceeds as joerd outlines above, is there a way that the code can be hidden within the VBA editor?

RE: Pre-set Termination Date for Spreadsheet

One Dumb Question....... Is there a way that you can overide this by approval initials, so you don't lose the formula's that are associated within the worksheet?

RE: Pre-set Termination Date for Spreadsheet

e.g. if the spreadsheet is used each year and by chance, any figures will not change for the following year.  

Not a big issue, was just wondering is all

RE: Pre-set Termination Date for Spreadsheet

bltseattle,

You can protect the VBA code, in my version go to Tools/VBA Project Properties/Protection, and complete the form.

Cheers,
Joerd

Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

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!


Resources