Solved: Using VBA to place a time limit on Word 2003 Docs

[mikemc]

Hi, I have a question about the ability to set a 'time limit' of sorts on Word 2003 template.

I need to use VBA to do either of two things:

1. Allow a user to only open the template a certain number of times (say, 10 uses) so that on the 11th time the template is opened, they receive some sort of message that the limit has been met.

or,

2. Allow a user to have unlimited access to open the template as many times as they want for a certain period of time (say, 6 months) so that on the expiration, they receive a message indicating the time limit has expired.

Any help on this is greatly appreciated!

Michael

[fumei]

User should never open templates anyway. Do you mean USE the template?

In any case, this sounds like a security/protection type of question. The bottom line with any security/protection question - regarding an Office product like Word or Excel - is that there is not any. Or little.

If someone is good, I am willing to bet that ANY solution to either scenario 1 or 2 can be cracked within 24 hours.

Can you set up something that will thwart basic users? Probably. Really knowledgeable users? Nope.

However, let's see....


#1

pick up the login username - not Word username as that is child's play to change

on document open, write two document variables. Username, and counter. Write the username to Username, and 1 to counter.

on NEXT document open; if document variable Username is login username, increment counter.

if counter > 10, message the user, close the file. They open it again...counter is still > 10...it still messages the user and closes the file.


#2 on document open, check for a wee text file somewhere. Where does not matter. Hide it.

If wee text file exists, open it.

If date in wee text file is current date - six months, then message the opener "No way Jose", and close the file.

If date in wee text file is less than six months prior to current date, continue to open the document.

If there IS no wee text file....then write one with the current date, ready for the next Open.

And there ya go.

Your basic user could be restrained by either of these, but a really knowledgeable one...not a chance. Slowed down perhaps, but not stopped.

Office files like Word and Excel are not, never have been, and I doubt ever will be, secure.

[Nelviticus]

I agree with Fumei, you can't lock down anything to do with Office without a lot of effort and even then it's very easy to circumvent. If you're doing it for your internal users it's not too bad but if you're doing it for external clients you should assume that it just won't work.

However ... I like this sort of thing and couldn't resist having a play. Stick the following code in your template and it will pop up a message and close the document if it's been used more than 10 times or for more than 180 days:
Public Sub AutoNew()

On Error GoTo Err_AutoNew

Const iMaxRuns As Integer = 10
Const lMaxDays As Long = 180

Dim iTimesRun As Integer
Dim dtFirstRun As Date

Dim bOKToRun As Boolean

Dim sTimesRun As String
Dim sFirstRun As String
Dim sErrorMessage As String

sTimesRun = GetSetting("Word", "MyKeySection", "TimesRun")
sFirstRun = GetSetting("Word", "MyKeySection", "FirstRun")

If IsNumeric(sTimesRun) Then
iTimesRun = CInt(sTimesRun)
Else
iTimesRun = 0
End If

iTimesRun = iTimesRun + 1

If IsDate(sFirstRun) Then
dtFirstRun = CDate(sFirstRun)
Else
dtFirstRun = Now
Call SaveSetting("Word", "MyKeySection", "FirstRun", CStr(dtFirstRun))
End If

bOKToRun = True

If iTimesRun > iMaxRuns Then
bOKToRun = False
sErrorMessage = "You have used this template " _
& iTimesRun & " times. The maximum limit is " _
& iMaxRuns & " uses."
End If

If DateDiff("d", dtFirstRun, Now) > lMaxDays Then
bOKToRun = False
sErrorMessage = "You have used this template since " _
& dtFirstRun & ". It can only be used for " _
& lMaxDays & " days."
End If

If bOKToRun Then
Call SaveSetting("Word", "MyKeySection", "TimesRun", CStr(iTimesRun))
Else
MsgBox sErrorMessage
ActiveDocument.Close (wdDoNotSaveChanges)
End If

Exit_AutoNew:

Exit Sub

Err_AutoNew:

MsgBox Err.Description
Resume Exit_AutoNew

End Sub
It uses the registry to store the date first run and the number of times run (GetSetting and SaveSetting). The key can be found in 'HKEY_CURRENT_USER\Software\VB and VBA ProgramSettings'. You should be able to hack it around to suit your needs.

Regards

[fumei]

1. registry code will only work if the person logged on has RegistryPermissions - can you be sure the person opening the document file does?

2. registry code will only work on THAT machine. If the document was copied/emailed...whatever...to any other machine, the counting logic will fail. A wee text file on a network server could be accessible at any time, in theory.

I am not arguing against using registry solutions. Just that detailed care must be taken.

[mikemc]

Thanks to both of you for the responses.

I'll try the code and see if it works for me. One question, though: How does that code recognize/determine which template is being used? Is it based off the filename? If so, I guess all someone would have to do is change the filename of the template and time starts over, huh?

Mike

[Nelviticus]

You put the code in the template itself, but the code as written will always write to the same key in the registry, so if you put it in ten different templates each one will increment the same "TimesRun" value when you use it. You should therefore replace "MyKeySection" with a unique value for each template.

As has been pointed out it's very easy to get around. What are you actually intending to use it for?

[mikemc]

Ok, thanks for clarifying the necessity to change the "MyKeySection"

I put the code into the template but because I already had a sub named "AutoNew", I had to change the name from "AutoNew" to "TimeLimit" [I changed all references in code].

For time-saving's sake, I changed the limit to 3 uses to test it, but it did not work. I was able to open it past 3 uses.

Any suggestions? Thanks!

Mike

[Nelviticus]

Whenever you create a document from a template Word will run any code in that template's AutoNew sub. You need to either put this code in that sub, or call it from there. If you've put it in a sub called TimeLimit, just add the line:Call TimeLimitat the start of your existing AutoNew sub.

[mikemc]

Perfect! Thanks. It worked like a charm that time.

I understand that it won't be 'completely' secure from hackers, but the people I'll be marketing this to are not generally considered to be highly computer savvy, so I'll have to accept the limitations on security.

On another note, now that I've locked myself out of the template [I created a backup without the time limit], is there a way to remove the time limit protection? I guess I'd need to modify the registry?

Thanks for the assistance!

Mike

[mikemc]

Also, I've never heard of a method of doing this, but is there a way to prevent a user from simply right-clicking on a template and 'opening' it instead of creating a new document by the traditional double-[left]click?

[fumei]

No. If they can see it, they can open it.

And thus goes one sense of security.....

But then:

1. user should never be able to get at a template (.dot) file anyway.

2. "now that I've locked myself out of the template [I created a backup without the time limit], is there a way to remove the time limit protection? "

Yes. Make it so it never locks YOU out. This is the first rule of doing any sort of locking/security design. If you extrapolate.....

It never locks you (as the developer) out, but DOES start your "security" functions if the opener is not you.