limiting functionality between dates

[Palmcrest]

I was wondering if there is a way of putting an expiry date in for using a template.
I can see this with an "if today > than XYZ date then message:"
What id like to do is limit the use for say 12 months, after which a box comes up to enter a code then it reactivates.
However Id only want to reactivate for another 12 months each time.

I was wondering if anyone has thought of this or is able to show how that would work,

thanks

 

[SkipVought]

HI,

So you want the xyz date to reset at some point to a date, xyz plus one year?

Please be more specific with how you want this to work.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Palmcrest]

So I want to open a template that looks at the current date.
If that current date is greater than a date in the code (Say 12 months from now) it comes up with a message to activate by entering a new key.
On entering the key the template has another 12 months grace
It then goes for another 12 months until it asks for another key to extend it for another 12 months and so on.

I hope that makes sense.

 

[combo]

The possible solution depends on application.
At least in excel new workbook is a copy of template, completely separated from the source file, but it inherits the code. You can use Workbook_Open code with testing Path to distinguish between new workbook and already saved one. A dummy sheet with hidden working contents can prevent from disabling macros.
Personally in such cases I prefer to separate code from end user workbook, either in add-in or in master workbook. I would rather create workbook by code and test current date for availability of this action.
You need a good place for storing data, a "VB and VBA settings" (as I remember) key in the registry is a safe place accessible by VBA GetSetting, GetAllSettings and SaveSetting functions.

combo

 

[Andrzejek]

Looks to me like "I am going to give you access to this file for a year, and if you want to use it for another year - you need a (new) key".

If that's the case, what would prevent the user to reset the date on the computer and 'pretend' it is still 2015?
And if user would get the new key to enter, how would you know (in code) that the valid key was entered?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Palmcrest]

Looks to me like "I am going to give you access to this file for a year, and if you want to use it for another year - you need a (new) key".

We cant change the system clock (Admin rights only)
So I thought this might be an idea.
We want to review for updates every 12 months so we either issue a new template or continue with the current.

 

[Palmcrest]

Got eveyone stumped? me too.

 

[SkipVought]

Your question is still vague.

But more important, the issue that you must solve FIRST is to guarantee that the user, when opening the "template", enables macros. Presumably, they could merely ignore the request to enable macros and still use your template indefinitely. Combo hinted at this, "A dummy sheet with hidden working contents can prevent from disabling macros."

So you need to work out a scenario to force the user to enable macros before they get to see anything of value in your workbook. This is not trivial.

Then, is the drop dead date a hard date, maybe a year from March 9, or a year from the time the workbook is first used? Does each user get his/her own year?



Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Palmcrest]

Thanks Skip
I see what you are elluding too. Since the workbook requires macros to be enabled for other functionality then its a given that enabling would be the case here.
Also I was thinking of a set date (in the future) for the one template that would be used by a few people at any given point in time.

So assuming Im not worried about macro enabling and system clocks and so on (I will deal with this seperatley)

Its easy to do something like "if today is > (set date) then msg "Not available for use" else carryon"
I was hoping to create something that asks for a key and then allows the user to continue.
The key resets the (set date) to another 12 months ahead and also generates a new key to repeat the process in 12 months time and so on.
Or points to a new key already in the code for the next 12 months ,
I was thinking I could store say 10 years of keys, one for each year in the code... Im not even sure if this could work or is feasable.

Hope that makes sense.

 

[SkipVought]

So if the Date > DropDeadDate Then
All relevant sheets get VeryHidden and workbook is saved in that state.
A Form sheet is displayed asking for a Key. Should there be a countdown timer here to close the workbook should the user not supply a valid Key within the given interval? Otherwise if the user fails to supply a key, the workbook is rendered unusable.

If the user supplies a valid Key, the DropDeadDate is advanced by one year and the relevant sheets are made Visible.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Palmcrest]

Thanks Skip - that looks like it. Im not worried about a countdown just an iput or cancel button I think.
Im ok with the verryhidden part , just not sure about using the key in the form.

 

[SkipVought]

Put a Structured Table named tDateKey like this in a VeryHidden sheet
[pre]
DDDate Key
2016/03/13 Key1
2017/03/13 Key2
...
[/pre]
In the same sheet have a Named Range for the CurrentKey, in this case Key1.

Use INDEX(tDateKey[Key],MATCH(Today(),tDateKey[DDDate],1)) to return the appropriate Key. If the returned Key vale does not equal the CurrentKey, then the user has exceeded the DDDate and must supply a new key.

I've done this table and formula by memory, since I don't have my laptop handy to check out the formula. It using the greater than Match Code. The table may need to be adjusted slightly.

If the user supplies the proper Key value, that value is then stored in the CurrentKey cell in the VeryHidden sheet. Otherwise the old Key value remains and the workbook relevant sheets remain VeryHidden rendering the workbook unusable.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Andrzejek]

We want to review [Excel template] for updates every 12 months so we either issue a new template or continue with the current.

Is it possible that you can simply set/send yourself a reminder to do it (review it) once a year?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.