Make a workbook tool expire and become unusable.

megnin

Active Member
Joined
Feb 27, 2002
Messages
340
I basicly want to make an order tool that will expire and force the customer to periodically download an updated tool with the most current part numbers.

What's the best way to make a workbook "expire" and prompt a customer to go download an new copy? Giving them a couple of weeks advance warning would be nice also.

Thanks!
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
Hi Megnin,

The easiest way I know to have a workbook expire is to put code in the workbook's Open event that checks a pre-loaded expiration date (could be just a constant in the code) against today's date, and if greater would then password-protect and hide all the worksheets and save the file.

Of course, the VBAProject should be protected so the users could not see the password. And of course all of this would be defeated if the user fails to enable macros on opening the file, so you should do something to force the user to enable macros. My recommendation is that you do this by starting out with the sheets hidden and protected, and having your Open event also unhide and unprotect the worksheets. Thus the workbook is unusable if the user doesn't enable macros.

You will have to add an extra (blank) sheet to your workbook since Excel does not allow all sheets to be hidden. This sheet is a good place to put a message (could be in a TextBox) telling the user that the reason he/she can't see the worksheets is that he/she didn't enable macros, and to close the workbook and re-open it with macros enabled.
 

megnin

Active Member
Joined
Feb 27, 2002
Messages
340
Thank you very much Damon.

I have the code to enforce macros.

Could I just include code to check the date with the enabled macros check and if the date has passed not unhide the main sheets?

Thanks!
 

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
Hi again megnin,

Yes, you are right. It doesn't make sense to unhide the sheets only to immediately re-hide them. I'm sure it would be disconcerting to the user as well to see them flash on the screen momentarily and disappear.

Damon
 

megnin

Active Member
Joined
Feb 27, 2002
Messages
340

ADVERTISEMENT

Thanks for the reply Damon. It's much appreciated!
 

Jack in the UK

Well-known Member
Joined
Feb 16, 2002
Messages
3,215
Hi to add to Damons advise

Be careful many guys i included can bust open hidden or expired passwords and any protections in less time than you can type them, so its not so secure.

However it will weak out basic users, nothin in Excel is that safe.

Jack

PS Posting just so you know its not so secure in any fact.
 

megnin

Active Member
Joined
Feb 27, 2002
Messages
340

ADVERTISEMENT

Thanks for the warning Jack.

This doesn't have to be iron clad secure.

It's an ordering aid and the customer's would gain nothing by cracking open the password or defeating the expiration date.

Thanks again.
 

Jack in the UK

Well-known Member
Joined
Feb 16, 2002
Messages
3,215
Hey thats good news, i get loads of stuff to open in my email ALL i refuse as i do not know whos who and if its the boss's documents.

Good news, i like the idea

Good luck.

Jack
 

megnin

Active Member
Joined
Feb 27, 2002
Messages
340
How do I refer to the date/time in VBA?

I've tried "11/4/2002 7:09:00 AM" and 37564.29792 and neither work in a comparison.

If now > "11/4/2002 8:05:00 AM" Then
... this always results in a true condition so I must be using it incorrectly.

Thanks for any advice!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,491
Messages
5,596,460
Members
414,069
Latest member
StudExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top