Automatic Sheet Protection - possible??

[RobbieR]

Is there any way that I can set it up on my sheet so that if the value of cell A1 is greater than Cell A2 then the sheet protection enables. The eventual aim of this excercise is that A1 will be "=TODAY()" and A2 will be a pre-set date, therefore if todays date exceeds the preset date, the sheet protection enables and no-one can add anything further to that sheet. Can this be done at all?

 

[SIXTH SENSE]

hi!
insert this on the Thisworkbook module

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_Open()
<SPAN style="color:#00007F">Dim</SPAN> sht <SPAN style="color:#00007F">As</SPAN> Worksheet
<SPAN style="color:#00007F">If</SPAN> Worksheets("sheet1").Range("a1").Value > Worksheets("sheet1").Range("b1").Value <SPAN style="color:#00007F">Then</SPAN>
    ThisWorkbook.Protect password:="workbookpassowrd"
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> sht <SPAN style="color:#00007F">In</SPAN> ThisWorkbook.Worksheets
        sht.Protect password = "worksheetpassword"
    <SPAN style="color:#00007F">Next</SPAN> sht
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[RobbieR]

Thats great. I have that working. Now... can I refine it further so that if they try to change anything (after it is protected) instead of the standard message saying "The cell or chart you are trying to change is protected blah blah blah". You can insert a user defined message?

 

[Mudface]

I don't think you can have a user-defined message to replace the protection warning unfortunately.