Ask Your Question

Calc Protect Cells with Macro

asked 2018-03-23 14:46:11 +0200

jeanDB gravatar image

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
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 ' 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.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2018-03-23 16:47:51 +0200

Jim K gravatar image

updated 2018-03-27 19:14:28 +0200

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 '
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, 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.


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 import ErrorCodeIOException
from import CloseVetoException

except exceptions.FileAccessError as exc:
data = None
        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:
except exceptions.FileAccessError as exc:
    if exc.msg.startswith("Error reading file"):
        raise exc
except FileNotFoundError as exc:
    raise exc
edit flag offensive delete link more


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

jeanDB gravatar imagejeanDB ( 2018-03-27 17:38:37 +0200 )edit

answered 2018-03-23 16:32:01 +0200

pierre-yves samyn gravatar image


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

See :


edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-03-23 14:46:11 +0200

Seen: 389 times

Last updated: Mar 27 '18