Date protect excel worksheet??

  fermerboy 23:16 09 Jun 2005

Hi All
I have a excel workbook that I wish to loan somebody but I want it to work on only one day.
My question is how to do this? I can get todays date to come up in a cell and then get IF function to give a true or false answer but then what? Ideally I'd like it to change something or move a formula so that it doesn't work right so what do i enter under false, can it be made to throw an error saying wrong date or something? Is there something else better to do this?

  tasslehoff burrfoot 23:51 09 Jun 2005

but maybe this will help until somebody (VoG, whisperer, simsy etc) come along.

You can put =today in a cell and the date on which you want the workbook to work in another.

As an example, put =today in A1 and 1/06/05 in B1

In the workbook open event put the follwing

Private Sub workbook_open()

If sheet1.cells(1,1) <> sheet1.cells(1,2) then activeworkbook.close

End sub

This will check if today is the date you want the sheet to work and close the sheet if not.


  tasslehoff burrfoot 23:53 09 Jun 2005

Sorry, just realised that disabling macros will get around this.



  fermerboy 00:14 10 Jun 2005

Thanks for that, something like that is what I'm after. Even if the sheet just won't work that will do.
Great nickname that!!!

  Simsy 08:23 10 Jun 2005

but a way of defeating the "disabling macros" trick is to "Very" hide the sheet, using a macro as it closes...

Using the "VeryHidden" property means it can only be "unhidden" using a macro... if macros are disabled then it can't be unhidden...

I'm assuming that the sheet that is to be hidden is called "main". The sheet that is seen when the workbook is opened is called "test".

When the workbook is closed the sheet is hidden using the following code;

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Sheets("main").Visible = xlSheetVeryHidden

End Sub

This means that it can only be unhidden using VBA/Macro...

On the sheet called "test" there is the following;

Cell A1 has the formula =Today()

Cell A2 has the date that you want it to be possible the see the sheet, e.g 10 June 2005

Cell A3 has the formula =A1=A2

Cell A3 has the name "validcheck"

There is a button called "cmdShow". When the workbook is opened the following code will check to see if it is the appropriate date for the sheet to be seen. If it is then the button is enabled and clicking it will show the sheet;

Code for check on opening;

Private Sub Workbook_Open()

If Range("validcheck") = "True" Then

Sheets("test").cmdShow.Enabled = True


Sheets("test").cmdShow.Enabled = False

End If

End Sub

the following is the code for the button cmdShow;

Private Sub cmdShow_Click()

Sheets("main").Visible = True


End Sub

It will also be necessary to lock the cells A1-A3, and protect the sheet, with a password, so they cannot be easily changed.

Note that I've done this rather quickly, (while still trying to do my job at work, and rather distrcted!), so It's not fully tested, and there may be snags that haven't occurred to me.

I hope it;

A) Works!

B) Does what you want.



  Rand Al Thor 08:35 10 Jun 2005

Tasselhof Burfoot. I believe that is a character from the Dragonlance Chronicles?

  fermerboy 08:59 10 Jun 2005

Fanstastic Simsy!!!
Couldn't have thought of that.
Way over my level of excel experiance.
I had just thought of sticking an extra IF in to the main formula to check for the todays date and then hiding the formulas etc. That is a better more secure way of doing it.

  VoG II 09:15 10 Jun 2005

There is always the cheap and nasty method

Sub Auto_Open()

Dim nw As Date, x, y

nw = Date

x = DateValue(nw)

y = DateValue("01-jul-05")

If x <> y Then Application.Quit

End Sub

  Simsy 11:41 10 Jun 2005

A compliment from VoG™ is praise indeed!


Note that the key bit in my approach is that the user disabling macros wont enable them to use the sheet in question.



  tasslehoff burrfoot 20:50 10 Jun 2005

You're absolutely right!

Simsy - brilliant!

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5T review: Hands-on

Illustrator Andrés Lozano on his improv line work, brazen use of colours & hand sketching

iPhone X review

Comment envoyer gratuitement des gros fichiers ?