Expiration

IsThereAWayOf

New Member
Joined
Sep 19, 2002
Messages
5
How can I set an expiration date to a workbook. For instance, I want a workbook's macros and fields to become unavailable after 12/31/03.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
I just want the workbook to become useless, basically. I did a half-a$$ way of making the workbook expire...basically, I have the "now" function in one cell and then all of my formulas basically use an if statement such as:
a1=now()

so...=if(a1>"12/31/03","EXPIRE",D1+B1)

I was hoping to come up with a more "proper" way of doing it. Perhaps by putting some code in the workbook itself.
 
Upvote 0
You can disable your macros with a date exit test. Like:

Sub myTime()
If Now() >= "9/20/2003" Then
MsgBox "Time is Up!"
'GoTo myEnd
End If
'The rest of your macro go's here.

myEnd:
Exit Sub
End Sub

If you password lock your macros from the VBA editor then it would be hard for the basic user to change your code.

You can add this type of code to an On Workbook Open Sub that tells Excel to close the Workbook after your date, much like the code above. That way the workbook will never open after your date and since the workbook will not stay open it is real hard to modify the code or break the password, but not impossible.

Note: If you do this and the user has their data in your application and you lock their data, you could be liable for the theft of their data even if they did not pay you for your application! JSW
 
Upvote 0
Yeah, I don't want to lock anybody out from seeing their data I just want it so they can't enter anymore data. I'm going to play around with the code you gave me and see what I can up with.

Probably the way I've been doing it is probably good enough.

THANKS!!!!
 
Upvote 0
Joe's final paragraph is crucial. I'm not a lawyer but there are certain legal issues involved here. I suggest that prior to starting a project you advise the users that on a certain date the workbook will be rendered useless (unless prior payment is received). This warning should be in writing. In addition, your invoice should contain a "retention of title" clause i.e. ownership of the intellectual property (the workbook and all macros) remains with you until the invoice is paid. Create a paper trail to protect yourself.

Perhaps you may consider a pre-expiration pop-up message "This workbook will expire in 10 days" (or whatever). If the users choose to ignore this, then it may mitigate any come back against you.


Regards,

Mike
This message was edited by Ekim on 2002-09-20 11:47
 
Upvote 0
Thanks for the advise. But this isn't for any financial gain or anything like that. It has practical uses. Basically I create logs and such where I work. I want to force people to start a new workbook at the beginning of each new year. So, I don't want the data to be locked, I just want to force people to start a new workbook to keep our records organized.
 
Upvote 0
What I have done is using Userforms to enter the data, I have a popup that says this is a 30 day trial period

Using this Code

Code:
Dim ws As Worksheet

Dim remaining As Integer




Private Sub UserForm_Activate()
    Application.OnTime Now + TimeValue("00:00:05"), "KillTheForm2"
Dim StartingDate As Date
StartingDate = #12/11/2002#  'wherever you stored the date when this file was first opened
If StartingDate > Now Then StartingDate = Now
Dim Different As Integer
Different = DateDiff("d", StartingDate, Now)
If Different > 30 Then Different = 30
If Different < 30 Then Different = DateDiff("d", StartingDate, Now)
If Different = 0 Then Different = 1



With ProgressBar1
    .Scrolling = ccScrollingSmooth 'my personal preference
    .Min = 1
    .Max = 30
    .Value = Different
End With

remaining = 30 - ProgressBar1.Value
    If remaining > 0 Then
     Trial_popup.Frameprogress.Caption = remaining & " Days remaining."
Set ws = ThisWorkbook.Sheets("Setup")
ws.Range("e23") = 1

Else
    If remaining <= 0 Then
    Trial_popup.Frameprogress.Caption = "Trial Period Expired."
    Set ws = ThisWorkbook.Sheets("Setup")
ws.Range("e23") = 2
    End If
End If


End Sub

Then on my Main Menu Userform, the Add Data Command Button is enabled or disabled depending on the value of the setup sheet e23 cell value They can do everything but add new data Using The Forms, And I have the sheets Protected so that they cannot enter it without using the form as the form unprotects and then updates the page and reprotects it, Now foolproof but enough frustration for many people

HTH

Derrick
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

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
Back
Top