worksheet should not open after a certain date

kris19382 · Jun 15, 2002

Hi,

I would like to make the spreadsheet not able to open after a certain date. Or make the data invisible after a certain date. Any ideas?

Thanks

PS I am putting in some tags in this email on which I could search for later.

SEARCH TAG: KRIS 19382 WORKSHEET INVISIBLE DATE OPEN

Juan Pablo González · Jun 15, 2002

You can do some things, but NONE of them are FULL bullet proof. This means it will stop the basic and maybe average user, but if someone wants to see your data, they will... is that ok ?

Nimrod · Jun 15, 2002

Hi Kris:

Here's a solution that might work for you.

Have the "Before Close" event in your workbook make all your sheets , except one, "xlVeryHidden".
Have the "Open Workbook" event unhide the sheets for you. If certain date criteria are met. If critera not met sheets are not made visible.

Explaination

You will want VB to hide sheets before closing so that disabling macros on open will not help the user. If they disable the VB no sheets will be visible
You do not want people being able to defeat you by setting the computer clock back either. To defeat this there is a veryhidden sheet that records today's date while opening. Once recorded the date stamp can only roll forward it can never roll back, therefore setting the pC clock back will not help.
Take note that I use "xlVeryHidden" property instead of just hidden. Three reasons I use these are that (a) they are less known (b) To get at them you have to go through the VBE ... which you will lockup
(c) hidden sheets are listed in the xls window while xlveryhidden are not even listed

TO CONFIGURE

Copy the following code into the "ThisWorkBook" object in the VBE
Make sure there is a "Sheet1" with nothing inmportant on it ie. one sheet must remain unhidden in excel
Create a Worksheet and name it "_" .. thats right call it underscore.
In cell "Z2" of sheet "_" type in the cut off date
Lock your VBE from prying eyes by going to VBE...Tools...VB Project Properties...Protection
Boomba close and Open and your all set up

On Open Event Code

Code:

Private Sub Workbook_Open()
' only increment date if larger then last recorded one
If Sheets("_").Range("Z1").Value< Date Or Not IsDate(Sheets("_").Range("Z1").Value) Then
        Sheets("_").Range("Z1").Value = Date
End If

If Sheets("_").Range("Z1").Value< Sheets("_").Range("Z2").Value Then
    For Each sh In Worksheets
     If Not  sh.Name = "_" Then
       sh.Visible = xlSheetVisible
     End If
    Next
End If
End Sub

On Close Event Code

Code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
For Each sh In Worksheets
 If Not sh.Name = "Sheet1" Then
   sh.Visible = xlSheetVeryHidden
 End If
Next
End Sub

What do you think ?

WARNING
Juan Pablo is correct in cautioning you that no solution is perfect. With my solution excel password cracking software could be used to crack the VBE password.
To make it harder you may also want to put the file in a secure encrypted NT directory so that looking at the file with a Binary editor won't show the contents of the file.
Then if we wanted to get really tricky we could get into some NT "second stream"Techniques as well Heh heh heh

This message was edited by Nimrod on 2002-06-15 19:12
This message was edited by Nimrod on 2002-06-15 19:18
This message was edited by Nimrod on 2002-06-15 19:40
This message was edited by Nimrod on 2002-06-15 19:52

Nimrod · Jun 15, 2002

Added Twist on the On Open event could also be to insert the Activeworkbook.close command as well.
Yes this can be defeated by disabling Macros but it adds another layer to the process.

On Open Event Code
Code:
--------------------------------------------------------------------------------

Code:

Private Sub Workbook_Open()
' only increment date if larger then last recorded one
If Sheets("_").Range("Z1").Value< Date Or Not IsDate(Sheets("_").Range("Z1").Value) Then
        Sheets("_").Range("Z1").Value = Date
End If

If Sheets("_").Range("Z1").Value< Sheets("_").Range("Z2").Value Then
    For Each sh In Worksheets
     If Not  sh.Name = "_" Then
       sh.Visible = xlSheetVisible
     End If
    Next
else

activeworkbook.close
End If
End Sub

--------------------------------------------------------------------------------

_________________
NOTE: Testing performed on Win2K utilizing Office 2000. Solutions may need tweaking for other versions. Adieu,Nimrod
This message was edited by Nimrod on 2002-06-15 19:27

elgringo56 · Jun 15, 2002

Thats kewl, Nimrod. If you dont mind, I will play with this to. I have been looking for something that I can put in programs that i make for people that will make them expire after a certian date if they havn't paid me for them. This may do the trick.

kris19382 · Jun 18, 2002

Hi Juan Pablo G. and Nimrod,

Thanks for your reply. I appreciate it.

I have 2 more questions.
1. Since I am a new user to VBE, I dont know if I am supposed to do anything after I cut and paste the code you were kind enough to put in your replies. I couldnt make it work. Would you please let me know what to do?
2. How can I prevent users from copying the data on my excel spreadsheet?.

Thanks in advance

Regards

PS: Juan I understand now why you are MVP and thanks Nimrod for monitoring this site. I am going to spread the word to all my friends that this is a great website.

Nimrod · Jun 18, 2002

Hi Kris:
The code must be copied into the "ThisWorkBook" object in the VB editor.
- Open VBE
- find "ThisWorkBook" object in smail upper left project window
- double click on "ThisWorkbook" object
- paste code in large right window.

kris19382 · Jun 18, 2002

Hi

Thanks. From your suggestions and stuff I read(my VBE experience is 5 days

) on your website I came up with a idea.

0. Put no important data on Sheet1
1. Turn off check for macros so that no popup appears when xls is opened
2. Put the below code (substitute 2002/06/19 with whatever date) in VBE

Private Sub Workbook_BeforeClose(Cancel As Boolean)
For Each sh In Worksheets
If Not sh.Name = "Sheet1" Then
If Date >= "2002/06/19" Then
sh.Visible = xlSheetVeryHidden
End If
End If
Next
For Each w In Application.Workbooks
w.Save
Next w
End Sub

4. Protect your VBE code with password
5. Save the worksheet

The pitfall is that if a person realizes that the protection level is set low for macros. If he chnages it to medium or high then he would get a warning and would be able to stop the w.save code from executing...Any ideas on how to get around that?

Regards

Nimrod · Jun 18, 2002

Yes Kris I do have ideas on all of that ... that is all I discussed in my first posting.
Please read it again and if you have any questions PM me ... cheers

worksheet should not open after a certain date

kris19382

New Member

Excel Facts

Juan Pablo González

MrExcel MVP

Nimrod

MrExcel MVP

Nimrod

MrExcel MVP

elgringo56

Well-known Member

kris19382

New Member

Nimrod

MrExcel MVP

kris19382

New Member

Nimrod

MrExcel MVP

Similar threads

Forum statistics

Share this page

worksheet should not open after a certain date

New Member

Excel Facts

MrExcel MVP

MrExcel MVP

MrExcel MVP

Well-known Member

New Member

MrExcel MVP

New Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock