Re: Protecting Cells from accidental change

[Adrian Custer <acuster gmail com>]

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:

http://office.microsoft.com/en-us/excel-help/overview-of-security-and-protection-in-excel-HP005238854.aspx

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

cheers,
  ~adrian



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
> > subject
> > > to accidental changes in future? It looks like gnumeric does not have a
> > cell
> > > protection function but I was wondering if some validation might be
> > applied
> > > 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
> > themselves.
> >
> > 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
> > values.
> >
> > > 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
> > could
> > > 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.
> >
> > Cheerio,
> >
> > Michael