Time Trial for Excel Sheets

[torque]

Is there a way to make an Excel sheet expire (ie. become useless or password protected) after a certain number of uses or days?

thanks!

 

[Smitty]

I dont know what DIM "ans" means. So I probably didnt do it.

You have the statement "Option Explicit" in your code, which means that you will be required to declare all variables. "ans" is a variable. Delete the "Option Explicit" portion of the code for now and ans will be accepted. (We can add it back in later - it's good practice).

As far as enabling macros - you now have them, so your Excel Security setting must be set to Medium, which requires the input.

You can set it to low while you're testing, but set it back if you don't have a virus scan program installed.

That's another consideration when distributing this to an outside client, but let's get this working first.

Smitty

 

[HalfAce]

OK, If it opens on "your" sheet and that is not the first sheet (as in sheet1) then you could go into the ThisWorkbook module and enter this:

Private Sub Workbook_Open()
Application.ScreenUpdating = False
Sheets(1).Select
Sheets("YourSheetName").Select
Application.ScreenUpdating = True
End Sub

If your sheet is in fact the first sheet, then just change the 1 to 2 or something.

If you really want to delete the entire workbook in the process, you can replace the code in the worksheet module with this:

Option Explicit 

Private Sub Worksheet_Activate() 

If Date > #6/7/2004# Then 
MsgBox "This worksheet has expired. (Pay up sucka!)" 
ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\My Documents\SomeAmbiguousName.xls"
    Kill "C:\Documents and Settings\My Documents\TheWorkbooksRealName.xls"
    ThisWorkbook.Close False
End If 

End Sub

This way the file still exists, but only you will know the name of it. If you know what other directories may be in their computer(s) you could hide it in one of them or just create your own to hide it in.

(Hey Smitty! How are ya?
I'm fine. The Missus is as ornery as ever! )
Dan

 

[daveus]

I was just creating some security for my workbook. I made a veryhidden file that I write to and immediately save. It adds one to the number every time and when it reaches a certain number...10 for example, the file will display a message and close. Then, if you reopen the file, it will display a message and close again. Seems to work pretty well. If you're interested, let me know...

Dave...

 

[HalfAce]

Hi Dave,
That's one of the other options I was thinking of too. (It just seemed too civilized!
Good call.

 

[daveus]

Thanks...I was trying to do something with the date as well, but I couldn't find a way to work around setting the date back to get around the security. Any thoughs halface???

 

[daveus]

While we are on the topic... How about code to remove a module or all the vba code. Anyone know how to do that? Remove code written in modules, this workbook, or for the userform??? I'd like to know that. That's actually what I was thinking about doing. I'd appreciate any help...

 

[torque]

daveus that sounds pretty good indeed. is it easy to implement? i am somewhat of a rookie if you haven't noticed...

Also, how do I un-veryhidden that worksheet?

 

[daveus]

Torque, this is what I wrote for mine...feel free to use it and change the sheet name and other parameters as necessary...

First, you need to add 0 in cell A1 in whatever sheet you decide to use. You also need to add your sheet name in the place of whateversheet.

to make your sheet veryhidden, It's easy to do in the properties window. In the VBE (visual basic editor), hit F4 and then select the sheet you want to hide in the project window. At the bottom of the properties window, you can select the visibility. Select xlsheetveryhidden. You can also use code to do it, but I would do it this way if I were you. To unhide it, just select it to be visible in the same window.

Sub ExpNoTimes()
Dim nmbopen As String, Nope as String, Nope1 as String
Activeworkbook.Sheets("whateversheet").Activate
nmbopen = Range("A1").Value
nmbopen = nmbopen + 1
Range("A1").Value = nmbopen
If nmbopen = 5 Then
Nope = MsgBox("This is the last time you will be able to use this tool.", vbCritical)

ElseIf nmbopen > 5 Then
Nope1 = MsgBox("You have exceeded the trial period." + Chr(10) + Chr(10) + _
"Excel will now close.", vbCritical)
Application.Quit
End If

ActiveWorkbook.Save
End Sub

 

[HalfAce]

Hi Dave,
Try this & see if it works.
Let’s say you specify a sheet for the workbook to go to on open. In that sheet you enter your expiration date in cell IV65535 and (as a one time shot), you enter the current date in IV65536.
Then in your workbook open event you could do something like this:

Private Sub Workbook_Open()
If Date < [IV65536].Value Or Date > [IV65535].Value Then
ActiveWorkbook.Close False
Else: [IV65536].Value = Date
End If
End Sub

This way if the current date (as the computer sees it) is later than your expiration date, (in IV65535) the workbook will just close upon opening. If not, it will put the current date in IV65536. If the clock gets set back and the workbook gets opened, the system date will be < than the date in IV65536 and will again, just close upon opening.
Seems to work with some (limited) testing....
Dan

 

[daveus]

Thanks Dan...believe it or not, I was trying to come up with something along those lines, just wasn't sure how to go about it. I'll give it a shot...if you come up with anything more "bomber" let me know. Thanks Dan for your help. Dan...by any chance is your last name McKendrick? Likely not...it's a pretty small town you live in up there. Thanks again!

Dave...