time limited exccel workbook

[CleGG]

is there a was to make an excel workbook only work for a certain period of time? and then after the date passes, the workbook no longer works or opens?

 

[Tom Urtis]

Hi Jon and CleGG - -

My point was, based on this that was written in the initial post...
"and then after the date passes, the workbook no longer works or opens?"
...why bother keeping the file at all? Just kill it if it is of no further use.

CleGG:
Sorry if I misinterpreted the intent of your post, but what would you do otherwise with a file that doesn't work or shouldn't be used?

 

[Jon Peltier]

Yeah, if the file is no longer useful, let it kill itself.

The point of my response, which quoted CleGG's earlier message, was to show how to make the file not work if someone bypasses the opening macro, and how to make the date checking macro smart enough to check for a reset system clock.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

 

[Jon Peltier]

i like this answer , but i am not verse enough to accomplish this. Can you walk me through this a little more?
thanks.

ENSURE THAT THE USER OPENS THE WORKBOOK WITH MACROS ENABLED.

This example has one working worksheet, "Working". To ensure that the user opens the workbook with macros enabled, introduce a second worksheet, "Warning". The following text is what appears on one of my own "Warning" sheets.
-----------------------------------------------------
Please enable macros when opening this workbook. If you disabled macros when you opened this book, please close it NOW without saving it, then reopen it with macros enabled.

If you cannot see any worksheets beside this one, you have opened the workbook with macros disabled. Close the workbook now WITHOUT SAVING CHANGES, then reopen it with macros enabled.
-----------------------------------------------------
This should be displayed in large red text with obnoxious thick borders and glaring background colors. The sheet should be password protected, and in VBA, use this command while constructing the sheet to prevent the user from being able to even select anything on the sheet:

activesheet.enableselection = xlunlockedcells

In the ThisWorkbook code module, you need these event procedures:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    lockup
End Sub

Private Sub Workbook_Open()
    un_lock
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Cancel = True
    lockup '' saves workbook in lockup routine
    un_lock
End Sub

These two procedures probably belong in a regular module, but I had left them in the ThisWorkbook module without ill effects:

Private Sub lockup()

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    ThisWorkbook.Worksheets("Warning").Visible = xlSheetVisible
    ThisWorkbook.Worksheets("Working").Visible = xlSheetVeryHidden

    ActiveWorkbook.Save

    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
End Sub

Private Sub un_lock()

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    ThisWorkbook.Worksheets("Working").Visible = xlSheetVisible
    ThisWorkbook.Worksheets("Warning").Visible = xlSheetVeryHidden

    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
End Sub

The idea here is to always save with only the Warning sheet visible, so the user must use VBA to use the Working sheet. You lock up the workbook before quitting, and unlock it when it opens with macros enables. Also, you intercept the user's save, lock it up, do your own save and cancel theirs, then unlock it again. This is how you assure that the user always opens the file with macros enabled.

When I used this most, it was when people were just learning about macro viruses, and some of my less sharp-witted colleagues thought macro and virus were synonymous. (One guy even thought the V in VBA stood for Virus!) I don't work there anymore.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

 

[Jon Peltier]

ENFORCED TIME LIMIT USING THE REGISTRY

The other aspect to this question is how do you put a time limit that is harder to circumvent by, say, turning back the system clock. I found this code on andreavb.com, and find that it works well enough for my present needs. If the module is protected, the user has a hard time figuring out what you're doing, even though it would be easy to muck around in the registry. You could make it harder by using APIs to hide deeper in the registry, or instead of the registry by using a file in the far reaches of the hard drive. Anyway, here's what I've used; the code works from any regular code module. If you know the file won't work anymore, you could use Tom's suggestion and code above, to make it delete itself.

''http://www.andreavb.com/tip080006.html
''Author
'' Reginald Wheat
''Language
'' VB5, VB6, VBA
''Operation Systems
'' Windows 95, 98 and NT

''-----------------------------------------------------------
Option Explicit

Public Function DateGood(NumDays As Integer) As Boolean
'The purpose of this module is to allow you to place a time
'limit on the unregistered use of your shareware application.
'This module can not be defeated by rolling back the system clock.
'Simply call the DateGood function when your application is first
'loading, passing it the number of days it can be used without
'registering.
'
'Ex: If DateGood(30)=False Then
' CrippleApplication
' End if
'Register Parameters:
' CRD: Current Run Date
' LRD: Last Run Date
' FRD: First Run Date

Dim TmpCRD As Date
Dim TmpLRD As Date
Dim TmpFRD As Date

TmpCRD = Format(Now, "m/d/yy")
TmpLRD = GetSetting(App.EXEName, "Param", "LRD", "1/1/2000")
TmpFRD = GetSetting(App.EXEName, "Param", "FRD", "1/1/2000")
DateGood = False

'If this is the applications first load, write initial settings
'to the register
If TmpLRD = "1/1/2000" Then
SaveSetting App.EXEName, "Param", "LRD", TmpCRD
SaveSetting App.EXEName, "Param", "FRD", TmpCRD
End If
'Read LRD and FRD from register
TmpLRD = GetSetting(App.EXEName, "Param", "LRD", "1/1/2000")
TmpFRD = GetSetting(App.EXEName, "Param", "FRD", "1/1/2000")

If TmpFRD > TmpCRD Then 'System clock rolled back
DateGood = False
ElseIf Now > DateAdd("d", NumDays, TmpFRD) Then 'Expiration expired
DateGood = False
ElseIf TmpCRD > TmpLRD Then 'Everything OK write New LRD date
SaveSetting App.EXEName, "Param", "LRD", TmpCRD
DateGood = True
ElseIf TmpCRD = Format(TmpLRD, "m/d/yy") Then
DateGood = True
Else
DateGood = False
End If
End Function

''-----------------------------------------------------------
''Usage

Private Sub Form_Activate()
If Not DateGood(30) Then
MsgBox "Trial Period Expired!", vbExclamation, "Unregistered application"
Unload Me
End If
End Sub

 

[Agamemnon]

I'm very interested in this topic and I would like to complicate things a bit more.

My question is: Suppose you imply a time limit on your excel workbook and it works until lets say 01/01/2004. I would like the vba module to ask for a new password (programmed in advance) that will work until 01/04/2004, and to repeat this every tree months. Can this be done


Wonder if someone can find a solution.
Agamemnon

 

[HalfAce]

I like Tom's solution. A true simple death code, & much more eloquent than what I've been using. (I'm also finding the exchange with Jon to be most interesting. I'll have to look into that.)

One question for Tom though is, you would place the code for the Workbook_Open, Close and BeforeSave events in the ThisWorkbook module, (right?), but where do you place it for a Deactivate event? In the same module?

Very interesting subject guys, thanks.
HalfAce

 

[Tom Urtis]

Hi HalfAce - -

You asked:
"One question for Tom though is, you would place the code for the Workbook_Open, Close and BeforeSave events in the ThisWorkbook module, (right?)"

Yes, that is correct, in the workbook module, unless you prefer the Autpen or Auto_Close approach; for differences & similarities of those vs Workbook_Open and _Close events, see a recent discussion here:
http://216.92.17.166/board2/viewtopic.php?t=61604&highlight=autoopen



You also asked:
"but where do you place it for a Deactivate event? In the same module?"

Yes, in the workbook module.

Regarding the Deactivate event, that'd be a decision for you the author/programmer based on the likelihood (or lack thereof) of the subject file remaining open over the calendar date of expiration. I guess you can be really thorough and place an OnTime event to kick in on the day before or the day of expiration, updated very few seconds or so to make sure the Kill takes place even if no user actions trigger an event, but that might be overkill (pun intended).

It'd probably be sufficient to place the Kill code in a module that is most likely to be triggered depending on the nature and use of the workbook. In my opinion, that is why, when the programmer writes an application, s/he not only needs to know how the workbook is to be used, but who is using it and why. There may be events not mentioned here that would be more appropriate, leaving it up to you to decide which is/are best based on the circumstances.

 

[HalfAce]

Hi Tom,
Thanks for the explanation. Actually (for my purposes) I could get away with this only being a Workbook_Open event...(We open a new "template" file every day and save it as the date.) I was just curious about the deactivate event because I'd never heard of it before.

Thanks again,
HalfAce