Pre-set Termination Date for Spreadsheet 1

[GregLamberson]

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:

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

 

[joerd]

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

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 for tips on how to make the best use of Eng-Tips.

 

[bltseattle]

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?

 

[Harley78]

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?

 

[IRstuff]

Why would you do that? The whole point is to prevent the sheet from being used again.

TTFN

FAQ731-376

 

[Harley78]

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

 

[IRstuff]

If it's used each year, then there is no termination date, is there?

TTFN

FAQ731-376

 

[joerd]

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 for tips on how to make the best use of Eng-Tips.