Calc Protect Cells with Macro

[jeanDB]

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:


with sheet

’ 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

.protect("")

end with*

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.

[jimk]

The code should look like this.

With sheet
    For colIndex = 4 To 6
        ' Allow column to be edited '
        cp = .columns(colIndex).CellProtection
        cp.IsLocked = FALSE
	    .columns(colIndex).CellProtection = cp
    Next colIndex
    ' turn on protection '
    .protect("")
End With

There is no way to specify ‘Allow all users of this sheet to: Select unprotected’ as there is when using the dialog.

Sad but true. According to Invoke Protection From Macro with 'Select Unprotected Cells' (View topic) • Apache OpenOffice Community Forum, the only way is to pop up the dialog using the dispatcher and require the user to uncheck the box.

If you want to go the extra mile, it may be possible to fully automate by saving the file in .ods format, unzipping and modifying the settings using an XML parser library. This can be done conveniently with a Python-UNO macro.

EDIT:

Here are some code excerpts that may help. Let me know if you want more details.

import re
import shutil
import xml.dom.minidom
import xml.parsers.expat
import zipfile

import uno
from com.sun.star.task import ErrorCodeIOException
from com.sun.star.util import CloseVetoException

try:
    self.make_temp_dir()
except exceptions.FileAccessError as exc:
    logger.warning(exc)
data = None
try:
    try:
        filepath = os.path.join(self.srcdir, 'content.xml'))
        dom = xml.dom.minidom.parse(filepath)
    except xml.parsers.expat.ExpatError as exc:
        raise exc
    data = self.readFile()
except zipfile.BadZipFile as exc:
    logger.warning(exc)
except exceptions.FileAccessError as exc:
    if exc.msg.startswith("Error reading file"):
        logger.warning(exc)
    else:
        raise exc
except FileNotFoundError as exc:
    raise exc

[jeanDB]

The XML idea is an interesting twist to the plot. Thanks for this. I am going to have a crack at it.
jeanDB

[PYS]

Hi

Unfortunately (afaik) there are no API methods for select-protected-cells.

See :

• tdf#55969
• tdf#43535

Regards