MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Close workbook by expiration date


Posted by M. Friedman on September 06, 2001 9:41 AM

I am trying to figure using a formula or a macro, how to have a spreadsheet that will automatically close itself after a set expiration date. Think of this as a fixed date of 12/31/01 and that if the NOW() is greater than the fixed date, the workbook automatically closes itself. The idea is to create Excel "shareware" that is only good for a period of time that I specify. If there is a fancier way to do it with a set starting date and then a time lapse in days after that, that's fine also.

Any help would be greatly appreciated.

fman99


Posted by Mark O'Brien on September 06, 2001 2:31 PM

I wouldn't follow that line of thinking, since the user could easily get around the function by just changing the date on their PC.

Posted by Jim on September 06, 2001 3:21 PM

If in the code on a given time or date all sheets
but 1 blank 1 are deleted and add a splash screen
set to 5 secs.that tells the user their out of
run time works, now even if they set the clock back
all they get is the 1 blank sheet.

Posted by . on September 06, 2001 5:02 PM

1717.html

Posted by Mark O'Brien on September 07, 2001 5:34 AM

Re: 1717.html

what if they're a really smart user that paid attention to the expiration date when they started to use the spreadsheet?

Of course, you could always put the initial start date on a worksheet that is "xlVeryHidden", change it to the current system time on that sheet at regular intervals and if the NOW() time was ever less than the last one of these, then format the users C drive and that will teach him for ever using your software.

Actually, I do quite like the "xlVeryHidden" idea, but it's going to take something a bit more fancy to protect a spredsheet sing VBA.

Posted by Heinrich on September 07, 2001 8:13 AM

Re: 1717.html


I think you're getting a bit carried away here. Most users don't know shit, and even if they do, the method suggested earlier on a previous posting seems quite good :-
1717.html

Posted by Jim on September 07, 2001 2:45 PM

Re: 1717.html i've used it and it works

The particular code in the archive posting is what
i was refering to. Once the run time has expired the original
file is deleted and replaced with a blank sheet or like i had mentioned
you could add a splash screen informing the user
is out of time.Since it's hardcoded and "protected"
you would have to know in advance that an expiration
even existed in the first place to look for it. With
all that said were all aware of software available
that can crack any VBA.

Posted by M. Friedman on September 08, 2001 8:02 AM

Thanks for all the help. I did get it to work per the code from Celia. the only problem is that if the user does not enable the use of macros on opening the file, then the spreadsheet is still available for use. I suppose there is no way to embed this function into the worksheet directly and not through a macro. I suppose I have to make the user accept use of macros to first "open" a "hidden" and locked worksheet so its useless not to do so.

Boy this is getting complicated. Is there a simpler way to protect an author's work?

Posted by . on September 08, 2001 5:18 PM

9550.html

Boy this is getting complicated. Is there a simpler way to protect an author's work?

Posted by Jim on September 10, 2001 4:36 PM

Try this, if you treat "not create or save as"
your workbook as an add-in it will probably do what
you want, put this code in your This Workbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.IsAddin = True

Private Sub Workbook_Open()
ThisWorkbook.IsAddin = False

now if the user chooses to disable the macros
they should be looking at a black screen.If
this is what you wanted, let me know, you can start
a new post because this one is getting buried fast.
Jim

Thanks for all the help. I did get it to work per the code from Celia. the only problem is that if the user does not enable the use of macros on opening the file, then the spreadsheet is still available for use. I suppose there is no way to embed this function into the worksheet directly and not through a macro. I suppose I have to make the user accept use of macros to first "open" a "hidden" and locked worksheet so its useless not to do so. Boy this is getting complicated. Is there a simpler way to protect an author's work?