Set trial time limit on workbook?

[samih]

Hello all, and sorry for my bad english

I want to create an excel file which duration of use is limited : after a pre-fixed date, user can consult sheet1 (protected) but unable to print it.

so I thought that I must create a macro to do the following steps :
1- set font color to black.
2- check the date, if it's before the pre-fixed date, allow printing , else deny it.
3- set font color when closing the file to white.

the white font instructions obligates the user to accept the macro or to add its signature to trusted signatures, else, he won't can even consult the file.

thank you for your help

[samih]

.

[Simon Lloyd]

samih look at this kb entry http://vbaexpress.com/kb/getarticle.php?kb_id=475&PHPSESSID=e2233cb412b0122f51f48707880a1d51

[samih]

first thank you very much for your iterest.

Second, since I'm beginner, I can not change the macro to fit my expects, so if you can give me the ready response, I will be very grantful.

For the file, I want that the user can consult it even after this limit date, the only thing I want to deny is printing the doc.


I found some interesting links, but I'm not capable to exploit them :

- deny printing :

Listing
Private Sub Workbook_BeforePrint
(c As Boolean)
c=True
MsgBox("Impossible to Print")
End Sub



- trial time limit :

Private Sub Workbook_Open()
If Date > CDate("29/01/2010") Then
MsgBox ("pas autoris?")
Application.Quit
ActiveWorkbook.Close
Else
MsgBox ("Attention, fichier non valide apr?s 29/01/2010")
End If
End Sub


thanks

[Simon Lloyd]

If you are not disabling the workbook or deleting it then macro's can easily be disabled on start up making your "security" useless!, anyway to stop printing after a date use the following:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If Date > "18/05/2008" Then
Cancel = True
End If
End Sub

[samih]

thank you very much, I know that disabling macros will cancel the printing interdiction, that why, I want to add to the maco the black and white font instructions.

by disabling the macro, the sheet font will be white, so impossible to read or to print datas, the activation of macro will be imperative to view correctly the sheet.
so the macro will :
1- convert automatically the sheet font to black (initiallay white)
2- make the date check
3- allow / deny printing
4 - convert shhet font to white when closing the file.

if you can complete the macro for me to do all these instruction, I'll be be thankful.
ps : what's your opinion about this idea of font color?

[Simon Lloyd]

Don't change the font its too much trouble, better if you hide the sheets like this: Create a worksheet called "Front" and put the macro below in your Workbook_Before_Close event, now when the workbook closes all sheets are hidden beyond the users capability, try opening your workbook without macro's enabled and you will only see the front sheet as the user cannot unhide them from the toolbar it can only be done via macro

Dim Sht As Worksheet
For Each Sht In Sheets
If Sht.Name = "Front" Then
Else
Sht.Visible = xlVeryHidden
End If
Next Sht
in your workbook open event you can do the opposite, show each sheet but hide the front sheet.

[Simon Lloyd]

P.S i will not complete the entire task for you as you will learn nothing, its better to give you the tools to make the journey to the conclusion yourself it promotes a better understanding!

[samih]

thank you very much, excellent idea.

I will try to prepare the final solution alone than post it here, than I will ask you to check if my work is correct. (it will take some days )

tank you one mor time. :thumb

[Aussiebear]

by disabling the macro, the sheet font will be white, so impossible to read

Type a value in a cell, change its font to white, select the cell and then look at the formula bar....

[samih]

my idea is that all the cells of the concerned sheet will be protected manually by excel protection (tools - protection - protect the sheet), this protection will not allow to user to select any cell.

To have a clear idea about the file, it contains many sheets for data input, and one result sheet, which essential use is to be printed. the protection will concern this result sheet only.
thank you for your interest