Re: Protecting Cells from accidental change

Oh, the poor outdated docs...

Looking into this issue, I can see there is work to do. First, Gnumeric has improved---fantastic! Second, the documentation of this issue was never complete to begin with, probably because of confusion as much as lack of time.

But first some questions. The Excel documentation:

discusses three levels of 'protection':
 1. Workbook
 2. Worksheet
 3. Cell
the first of which seems, due to encrypting the contents, to be some source of securing rather than merely protecting. The other two are really about simple protection rather than formal security.

In Gnumeric, Workbook protection can be done in the
    View Menu > View Properties... > Protection
setting, where a simple checkbox allows 'protecting' the whole workbook. While version 1.10.17 (the latest that I have working on Mac) has a password field, I see no way to make it active. Am I right that it does not work? If so, I take it that workbook protection in Gnumeric acts like 'protection' and not securing the file.

Worksheet protection can apparently be done in several ways:
 1. In the context menu on the worksheet tab there is a 'lock' icon
    that can be toggled on and off
 2. The same dialog can be reached from the 'Format' > 'Sheet' menu
 3. In the cell formating dialog, there is a checkbox 'protect
    workbook' reached by the 'Format' > 'Cells' > 'Format...' entry
which appear to toggle the same preference since they alter each other.

Both of those work as I expect, after locking, edit attempts pop up a new error window.

Cell protection, however, has some complex interplay with worksheet protection that I am not sure I fully understand. It seems that cell protection is a dual:
 * set 'protect' flag on worksheet
 * set 'lock' flat on cell
and that ultimately there is no 'worksheet' protection. Indeed, it seems that all cells have the 'lock' flag set by default!? I take it that is merely to make the 'worksheet protection' work as expected even if it makes the cell protection upside down.

So if one wants to protect certain cells in a worksheet, does one actually have to:
 1. Unset the 'lock' flag on all editable cells
 2. Set the 'protect' flag on the worksheet
or is something else going on? If so, cell 'lock' is really, the reverse. Cells start as 'locked when worksheet protected' and, under active user intervention, can be switched to 'allow editing even when worksheet protected'. Is that a fair summary?

Answers and clarification would be appreciated.


On 9/13/14 4:32 AM, Steve Greig wrote:
Thanks to all for information and ideas. The documentation was a little
misleading on that point and I have now achieved the main thing I want
which is to be able to protect the work I have done but leave remaining
rows unprotected so I can easily add new data. I will only have to
temporarily unprotect the sheet to correct mistakes. Automatically
protecting cells that I have entered information into would be nice as then
I would not have to protect them for example at the end of each session of
data entry and also they would be protected during that session. I was
wondering if the protection could be applied to cells on the condition that
they were not empty.

Michael, I will play around with that idea. It does sound complicated but
might be useful in some situations.

Best wishes Steve

On Sat, Sep 13, 2014 at 7:27 AM, Michael Uplawski <
michael uplawski uplawski eu> wrote:

Good morning,

I have a suggestion, but it may not be applicable to a Gnumeric table
which is already in use or has evolved past the stage, where
modifications are easy.

On Fri, Sep 12, 2014 at 09:35:04PM +0100, Steve Greig wrote:

My question is what is the best way for me to make certain cells less
to accidental changes in future? It looks like gnumeric does not have a
protection function but I was wondering if some validation might be
which would make the cell(s) protected. The protection would need to
allow the
results of formulae in the cells to change but not the formulae

I suggest that the formulae which are usually there to calculate
“results”, be it intermediate results, are accumulated in one table
(“sheet”) and the original data, which you may wish to adapt or complete
frequently, stay on a different sheet, where they are looked up in your
“results-table”. This way, you can easily protect the whole sheet, which
protects securely your formula without impeding calculations on new

Would it be possible for every cell I have entered something into to then
automatically be weakly protected ie. if the value is changed a pop up
say 'do you really want to change the value of this cell'?

If you use the existing protection on whole sheets, a message-box is
already generated each time, that you try to alter cell-content.



[Date Prev][Date Next]   [Thread Prev][Thread Next]   [Thread Index] [Date Index] [Author Index]