Upon closing a file each new row of entries in a spread sheet becomeread-only

[Michael Lanier]

I have a business spreadsheet. I want each new row of data to be made
unchangable (read-only) when the file is closed so entries will be
permanent when the spreadsheet is opened the next time. Changes need
to be able to be made during the session. If the information is
entered in rows 10 through 20 and columns A through M, I want only
these rows and columns to become read-only when the file is closed.
Anything outside of this range cannot be made to permanently change.
Most of these entries will reflect values from formulas. Can it be
done? Thank you for any help you might offer.

Michael

 

[Project Mangler]

Michael,

I'm new at this but having played around here the following will protect
cells in A10:M20 which have text, numbers or formulas in them.
Put the code in Thisworkbook.
The protection is applied the next time you open the workbook.
It allows blank cells in range A10:M20 to be changed but not cells which
were not blank before the workbook was closed.
I hope this is what you were trying to describe.

Private Sub Workbook_Open()
Worksheets("Sheet1").Unprotect
Worksheets("Sheet1").Range("A1:IV65536").Locked = False
Worksheets("Sheet1").Range("A10:M20").SpecialCells(xlCellTypeConstants).Lock
ed = True
Worksheets("Sheet1").Range("A10:M20").SpecialCells(xlCellTypeFormulas).Locke
d = True
Worksheets("Sheet1").Protect
End Sub

DB

 

[Michael Lanier]

Thanks DB. I'll put it to the test tomorrow. It looks good except
I'm not sure yet how to generate a "trigger" that will cause the
applicable row(s) to protect. I suspect that if for example cell Z12
were to meet a condition, then the condition would cause the rest of
row 12 to protect. But somehow, I would have to build this into the
macro so that everytime a condition was met in column Z, the
applicable row(s) would protect. Regardless, you've given me
something to think about. Thanks.

Michael