Bug 132133 - Allow protecting only the format of a sheet
Summary: Allow protecting only the format of a sheet
Status: RESOLVED DUPLICATE of bug 88108
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
6.3.3.2 release
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: needsUXEval
Depends on:
Blocks:
 
Reported: 2020-04-15 19:12 UTC by Christopher R Lee
Modified: 2020-09-08 08:36 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Christopher R Lee 2020-04-15 19:12:55 UTC
I've designed a spreadsheet with particular cell borders, column heights & widths, and font sizes. Nothing elaborate.

My students have to enter data by whatever means they wish, but without altering the formatting; for the spreadsheet to be of any use, the appearance of the data has to remain compliant with a particular pattern.

I can't find a way of protecting only the formatting of a sheet.

Paste-Special, and macros which I don't know how to write (in the absence of an ecosystem to match VBA), are not solutions for 12-year olds.

Bug 88108 dating from 2015 is nearly a duplicate, but apparently there has been no action on the necessary changes to the odf standard.

I've marked this report critical because it concerns one of the missing features that are critical for a competitive position of LO in the educational field.
Comment 1 m.a.riosv 2020-04-15 21:10:36 UTC
There is a way or trick, Menu/Tools/Share Spreadsheet. It protects for changes on formatting atributes and some functionalities, there is more detail in the option.

https://help.libreoffice.org/7.0/en-US/text/shared/guide/collab.html?System=WIN&DbPAR=CALC&HID=modules/scalc/ui/sharedocumentdlg/dialog-action_area1#bm_id8779565

Can it solve your question?
Comment 2 Christopher R Lee 2020-04-16 07:05:58 UTC
(In reply to m.a.riosv from comment #1)
> There is a way or trick, Menu/Tools/Share Spreadsheet. It protects for
> changes on formatting atributes and some functionalities, there is more
> detail in the option.
> 
> https://help.libreoffice.org/7.0/en-US/text/shared/guide/collab.
> html?System=WIN&DbPAR=CALC&HID=modules/scalc/ui/sharedocumentdlg/dialog-
> action_area1#bm_id8779565
> 
> Can it solve your question?

Not at all.

1) The file will be distributed by various means (mail, usb key, downloading) which would break shared mode. Using a shared folder on a fileserver or cloud storage is not an option here, particularly with children.

2) The information supplied in the 7.0 help and in the 'Partager le Document' (6.3.3.2, French) window is neither complete or precise: colours are mentioned but not borders or column & row widths.

3) The same question also concerns business users. Based on pre-retirement experience before 2007 with XL 2003 and earlier, sharing is not appropriate for many business applications. Spreadsheets need to be password-secured by the creator / manager so that end users are limited to typing or copy/pasting alphanumeric data into designated pre-formatted cells. The finished document must retain all other content and formatting because it constitues a report or certificate. Note that applications were too numerous, varied and urgent for conventional software development by an IT service to be envisaged, hence the widespread use of XL (often with VBA), but not to date of Calc.
Comment 3 QA Administrators 2020-04-17 03:36:17 UTC Comment hidden (obsolete)
Comment 4 Esben Jensen 2020-04-27 13:31:39 UTC
I think this would be very handy as well. When working with a spreadsheet that has formatting such as borders, copy/pasting or cut/pasting quickly becomes a chore when every action has to be done with paste special.

And as the OP mentions, untrained individuals will quickly mess up formatting when it's not locked.
Comment 5 Christopher R Lee 2020-04-27 14:46:21 UTC
(In reply to Esben Jensen from comment #4)
> I think this would be very handy as well. When working with a spreadsheet
> that has formatting such as borders, copy/pasting or cut/pasting quickly
> becomes a chore when every action has to be done with paste special.
> 
> And as the OP mentions, untrained individuals will quickly mess up
> formatting when it's not locked.

I agree, and this remark also applies to Writer regarding the format of pasted text. A workaround would be to memorise a macro (no need to understand the code), but the macro facilty lacks a little wizard for easily assigning the macro to a clickable object.
Comment 6 Christopher R Lee 2020-08-23 19:40:39 UTC
I note that the Importance has been assigned to 'medium' - 'enhancement' which, in the drop-down list, seems to fall somewhere below 'trivial'.

During the last few months, I have been helping someone set up a micro-enterprise, using LO for the office work. While (under French regulations), the administrative burden has been made as light as possible, I have been unable to create Calc spreadsheets (invoices, etc.) that have proved sufficiently reliable in the hands of users inexperienced in office IT. The survival of such small companies depends on reliable documentation and record keeping.

Consequently, we are now searching for funds to invest in the Microsoft 365 Business Standard package. The cost is about 300 euro/year for two users, though the package includes online storage for which we are currently paying another supplier. This migration will prevent us from using machines, available at low cost because they are no longer usable with Windows, that have successfully been converted to Linux.
Comment 7 m.a.riosv 2020-08-24 07:27:44 UTC
I think you are misleading what 'enhancement' means, this report is a request for enhancement, a new option on sheet protection. It is not a bug, something not working as should be.
Comment 8 Christopher R Lee 2020-08-25 17:04:16 UTC
(In reply to m.a.riosv from comment #7)
> I think you are misleading what 'enhancement' means, this report is a
> request for enhancement, a new option on sheet protection. It is not a bug,
> something not working as should be.

I would agree that ‘enhancement’ can include requests that are more important than ones that might be dismissed in the old jargon as ‘nice to have’.

However, I think the present subject falls into a category that might be defined as ‘major functionality inexplicably omitted from user-requirements and design specifications’. It may not, therefore, be a suitable subject for the bug reporting environment.

Here, I’d define ‘major functionality’ as something people working in major fields such as business and education expect to find, and can’t do without. To judge from the plethora of information on the web about the corresponding function in MS Excel, it is in widespread use - though apparently not as easy to use as some users would like !

Potential users of LO might consider an omission like this symptomatic of design and user feedback procedures needing revision, and may walk away without providing any kind of feedback.
Comment 9 andreas_k 2020-09-08 08:34:49 UTC
Tbh. the bug report didn't get fixed faster if it's an enhancement or major feature.
Comment 10 Heiko Tietze 2020-09-08 08:36:58 UTC
(In reply to Christopher R Lee from comment #0)
> Bug 88108 dating from 2015 is nearly a duplicate, but apparently there has
> been no action on the necessary changes to the odf standard.

Which is not a reason to file another ticket. As long nobody volunteers to implement this enhancement we have to wait. QA sets a higher importance transparently on factors like numbers of people on CC, numbers of duplicates etc.

(In reply to Christopher R Lee from comment #6)
> Consequently, we are now searching for funds to invest in the Microsoft 365
> Business Standard package.

Do you believe a request for enhancement would be listened? Besides all the open vs. closed, paid vs. free, community vs. capital discussion. Rhetorical question, no need to discuss. MSO is doing great work, of course, and maybe protecting the format has been implemented in Excel for years.

*** This bug has been marked as a duplicate of bug 88108 ***