Control useful life of workbook?

[RickGreg]

I have created a multi-sheet workbook for a client and I¹m trying to come up
with a way to have it ³expire² after a certain date. For example, I¹d like
for it to become unusable after Dec. 31 unless the comes back to me to
provide clearance for them to ³re-new² their ability to use it for another
year (with appropriate updates).

Can anyone suggest a simple technique for accomplishing this?


Excel 11.5.3
OS 10.5.6

Thanks!

 

[CyberTaz]

How "simple" would depend on your proficiency with VBA There is no
setting within the file that will cause it to self-destruct at a specific
point in time, but perhaps the code could be written in to accomplish this.

Whether it would be effective, however, is a different matter - even with
the appropriate code the file will continue to be usable after the
expiration date if being used in Office 2008 because VBA isn't supported.

Stay tuned, though - I'm sure there will be other commentary to come.

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[RickGreg]

Some added detail... Most users will be on Windows version, so lack of VBA
should not be a major problem.

I do not want the file to literally "self-destruct"... Just become unusable
until I re-bless it.

I was wondering if there is something creative I could do with conditional
formatting based on a date, but my experimentation has not yielded any
success.

Thanks again.

 

[CyberTaz]

Hi Rick -

Welllll, I didn't literally mean self-destruct

Not being a programmer I can't tell you exactly how to go about it but what
I'm conceptualizing is...

A VBA procedure (macro) can be constructed to test the date the file is
opened to the expiration date specified in the code. If the launch date is

= to the exp date the code would proceed to activate protection on - or

"lock" - the workbook file to prevent editing, provide a notification prompt
to the user, etc. That procedure would be set to run each time the file is
opened. Trying to include some sort of "reactivation" mechanism might be a
little trickier but I'm pretty certain it could be done.

What you're actually talking about is creating a custom application using
Excel as a foundation. But like I said, it will take a decent programmer to
get the job done properly. If you aren't a programmer or don't have one "on
staff" you'll probably have to hire someone to write the code. There are a
few pros who monitor this group so perhaps you'll get some better input.

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[Bob Greenblatt]

I have created a multi-sheet workbook for a client and I¹m trying to come up
with a way to have it ³expire² after a certain date. For example, I¹d like
for it to become unusable after Dec. 31 unless the comes back to me to
provide clearance for them to ³re-new² their ability to use it for another
year (with appropriate updates).

Can anyone suggest a simple technique for accomplishing this?


Excel 11.5.3
OS 10.5.6

Thanks!

PMFJI, but by using an XLM macro, you can accomplish this easily on both
platforms with the same code. Of course, like with VBA, or any other
solution, a determined and clever user could probably "break" in after the
expiration date. Using conditional formatting is just one way you could do
this without code, however, undoing it if the user resubscribes will
probably require code.

 

[CyberTaz]

No "PMFJI" required - in fact I was hoping either you or John would & would
have contacted you directly if you hadn't This is more up your alley
than it is mine - Taz don't do code :-}

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[Laroche J]

I happened to create a workbook with an expiry date a few years ago.

In a cell of a hidden row I put the last year the workbook could be used.
Then in a formula I put
=IF(YEAR(TODAY())<=ExpiryYear, **put here something to do when not
expired**, "program expired"). Adapt to your needs.

If you lock the sheet with a password that should be quite efficient.

For reactivating the worksheet, you could provide them with a passcode to
enter in a marked cell, that would in fact be a kind of disguised and
encoded expiry year. A formula (or many) could split the passcode in two,
perform some operations on each part, see if the two results match, and if
they do create the expiry year from the result (or from another part of the
passcode). If the user tries random numbers (or letters) that don't pass the
match test, set an expiration date of 0. All of this processing should be
hidden of course.

For your own use, make sure to create a spreadsheet with the reverse
formulas, to easily create passcodes based on the desired expiry year and a
seed you provide for "randomness".

JL
Mac OS X 10.4.11, Office v.X 10.1.9




RickGreg wrote on 2009-03-19 08:42:

 

[RickGreg]

JL-

Thanks. So if I understand correctly, this is not actually intended to make
the sheet non-functional, it would just create a warning message indicating
that the file has expired?

If I have this message displayed in prominent places, it should discourage
the users from using.

I suppose I could use other if() statements to replace key entries if the
expiration date has passed.

Thanks.

-RG