I am trying to implement cell protection using a macro that would work exactly as it does using the 2 protection dialogs. Viz Format Cells → Cell Protection ->Protected Tick box ticked for those cells to protect and unticked for those cell that can be edited. Then going to Tools → Protect Sheet and ticking under 'Allow all users of this sheet to: Select unprotected cells, leaving all other options unticked.
The effect of this is as I would expect:- No protected cells can even be selected, let alone altered, but unprotected cells are selectable/editable.
My attempt at achieving this effect with a macro is as follows:
’ Allow these 3 columns to be edited
.columns(4).CellProtection.IsLocked = FALSE
.columns(5).CellProtection.IsLocked = FALSE
.columns(6).CellProtection.IsLocked = FALSE
’ turn on protection
I have also tried Locking Cells as each is filled with data:
*sheet.getCellByPosition(5,row).CellProtection.IsLocked = FALSE*
The result is that all cells are still selectable but attempts to edit even the unprotected cells produces a dialog box “Protected cells cannot be modified”
It strikes me as odd that when calling the ‘protect("")’ method there is no way to specify ‘Allow all users of this sheet to: Select unprotected’ as there is when using the dialog. I have searched for a solution in Andrew Pitonyak’s ‘OpenOffice.org Macros explained’ and also the OpenOffice documention but to no avail. I am new at this anyway. I am not wanting to do this for reasons of security, simply to help avoid accidental changing of data.
A pointer in the right direction would be much appreciated.