unlock cell on exit

[manmah]

Please bear with me while I try to explain this..
I have a workbook which information is fed in through the frontpage. On running the MACRO the information is then sent to numerous other pages for working out costs, this then clears the boxes ready for the next batch of information. Unfortunately due to human error some errors do occur and my boss wants this spreadsheet to be perfect.
What I want to do is this:
I want to enter the date into the box and then I want the box to lock so that when the information is sent this box won't clear and the box will still contain the date entered. I have worked out how to lock a cell depending on information entered into another cell, and presume I can doctor that VBA so it works in the same way, but the other thing I want to do is for the box to then unlock when the spreadsheet is closed down so when it is re-opened the date can be changed to the new one????
Possible??????
Thanks
Mark

 

[Jaafar Tribak]

What do you mean by boxes ? Are you referring to worksheet cells or to something else ?

 

[manmah]

Sorry yes boxes I mean cells
Thanks

 

[Jaafar Tribak]

I guess you could lock the cells concerned and protect the worksheet when opening the workbook and then reverse this action ipon closing. Something along these lines :

Private Sub Workbook_Open()

    Sheet1.Cells.Locked = False
    Sheet1.Range("A1:A10").Locked = True
    Sheet1.Protect

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Sheet1.Unprotect
    Sheet1.Cells.Locked = True

End Sub

Where cells A1:A10 are the cells you want locked. change those cells and worksheet as required.