Description: Not a bug but a missing feature. It would be nice if Conditional Formatting could be protected from gettimg overwritten by copy&paste. I know of ctrl+alt+shift+v but its a pretty annoying key-combo and users are users and do user-stuff. It would also be nice if the window of Conditional Formatting could be resized because horizontal scrolling is annoying if there is a longer function. Steps to Reproduce: 1. creating a conditional format 2. copy a cell from outside the range of the formatted cells into the formatted area Actual Results: the format of the conditional Formatting gets overwritten and the range is automatically changed (it removes the whole line from conditional formatting) Expected Results: it shouldn't be possible to overwrite within the range of a conditional formatted area Reproducible: Always User Profile Reset: No Additional Info: Version: 7.6.2.1 (X86_64) / LibreOffice Community Build ID: 56f7684011345957bbf33a7ee678afaf4d2ba333 CPU threads: 12; OS: Windows 10.0 Build 22621; UI render: Skia/Raster; VCL: win Locale: de-DE (de_DE); UI: de-DE Calc: CL threaded
And how to do when you want to overwrite? Instead of the long shortcut, you can create your own. Menu/Tools/Customize/Keyboard.
Hi thanks for your quick answer. There should be a toggle/checkbox inside the conditional formatting screen to prevent the conditional formatting from getting overwritten. Maybe an additional option in the cell protection window to only protect the format of a cell would solve the issue too. From google search i learned that i am not the only one with this problem. In excel there is a vba-script that resets the format after a manual change but makros and scripts are not allowed in my job. Changing shortcut is not an option because most libre office functions are locked for security reasons beside the fact i had to change shortcuts for 24 users.
What if the source/target have cell styles? Would you expect a protection (beyond what we have) in any formatting case, or if this is just about CF, what makes it so special?
Created attachment 191863 [details] calendar with conditional formatting
i have a sheet that is used as calendar where employes enter vacation, homeoffice, days off (flexible working) and physical presence on bridge days. The calendar calculates all holidays, bridge days, weekends for this and every future year. so cell formatting changes each year. If i had a checkbox to lock cell formatting in conditional formatting this would solve the problem of manually formatting cells every year. I know it would be faster to create such a calendar each year manually, but this is a boring task that i want to bypass. I want to solve this problem once and for all and never touch this thing again. But right now the formatting is getting killed by the employees copying cells. On the other hand it doesn't matter cause it would be enough if could lock the formatting of the whole calendar, but only from manual changes.
(In reply to phantomlord2 from comment #5) > If i had a checkbox to lock cell formatting in conditional formatting... Tools > Protect Sheet: + [ ] Edit values (if checked you permit editing cells) Interestingly the CF neither applying a cell style is not blocked by the protection (yet, I'd treat this as a bug) but ordinary formatting like text/background color. Another option for the sheet protection could be Tools > Protect Sheet: + [x] Format cells (if checked you permit any formatting including DF, CF, and styles).
Yes this sounds like a good idea. When a user tries to copy&paste a cell or is using the fill handler in a protected sheet it would work like strg+c strg+shift+alt+v I'd love that.
If the sheet would priorize CF over manual formatting it would also work, didn't know it should work like this.
Eike, any blocker for comment 6?
The "Edit values" values option doesn't make much sense to me, protecting a sheet to allow editing on protected cells.. it doesn't tell what would be protected then. But more, all code over the place asks whether the sheet and then cell is protected and if so denies content changing actions or skips cells when traveling; with that option it would also have to ask whether that option is enabled if the sheet is locked. Not good. Similar for "Format cells" protection that's just another wording reversing the state to ask. For this to take effect and be disabled if sheet protection is on it still would disallow editing on protected cells, so wouldn't solve the request. Or if that was to apply on non-protected cells it's confusing UX. Or did you mean to have both? That's utterly confusing. I'd rather introduce an option like "[ ] Lock all formatting even on unprotected cells" because that is what is actually requested here. (In reply to phantomlord2 from comment #8) > If the sheet would priorize CF over manual formatting it would also work, > didn't know it should work like this. Prioritize where? Copy-pasting a cell also pastes the "this has no CF" attribution, if so, unless excluded with Values Only. That won't change.
At least those changes are not saved if Menu/Tools/Share documents, is active.
(In reply to Eike Rathke from comment #10) > I'd rather introduce an option like "[ ] Lock all formatting even on > unprotected cells" because that is what is actually requested here. this would work for my sheet i guess but what is the difference to this: > Another option for the sheet protection could be > Tools > Protect Sheet: + [x] Format cells (if checked you permit any > formatting including DF, CF, and styles). ?
(In reply to Eike Rathke from comment #10) > Or did you mean to have both? That's utterly confusing. Yes, both. The example is a vacation planner/tracker where users copy/paste data points (eg. "x") from week 1 into 2. And the sheet author wants to allow this but asks for a way to keep the CF. My idea was to "[x] Lock all formatting" + "[ ] Disable editing values" (to turn the logic around here; this would be on by default). > I'd rather introduce an option like "[ ] Lock all formatting even on > unprotected cells" because that is what is actually requested here. Cannot wrap my mind around the "even on unprotected cells". This wont be possible in the sheet protection dialog and requires some additional protection method. (In reply to Eike Rathke from comment #10) > ...code over the place... Sounds like some effort.
(In reply to phantomlord2 from comment #12) > (In reply to Eike Rathke from comment #10) > > I'd rather introduce an option like "[ ] Lock all formatting even on > > unprotected cells" because that is what is actually requested here. > > this would work for my sheet i guess but what is the difference to this: > > > Another option for the sheet protection could be > > Tools > Protect Sheet: + [x] Format cells (if checked you permit any > > formatting including DF, CF, and styles). It is unclear to what cells the option applies, because normally all protection applies only to protected cells, but not to unprotected cells. Now if checked, would that mean that formatting protected cells would be ok? And if unchecked, why would it prevent unprotected cells from being formatted? (In reply to Heiko Tietze from comment #13) > (In reply to Eike Rathke from comment #10) > > Or did you mean to have both? That's utterly confusing. > Yes, both. The example is a vacation planner/tracker where users copy/paste > data points (eg. "x") from week 1 into 2. And the sheet author wants to > allow this but asks for a way to keep the CF. My idea was to "[x] Lock all > formatting" + "[ ] Disable editing values" (to turn the logic around here; > this would be on by default). "Disable editing values" is the standard case for protected cells in a protected sheet. Making this explicit and then even allowing to disable it is confusing. > > I'd rather introduce an option like "[ ] Lock all formatting even on > > unprotected cells" because that is what is actually requested here. > Cannot wrap my mind around the "even on unprotected cells". This wont be > possible in the sheet protection dialog and requires some additional > protection method. But that is exactly what is requested here. The protected cells are of course locked in a protected sheet, but for non-protected cells where one enters or pastes values to, modifying CF is also to be disallowed. I don't get why that should not be possible in the sheet protection dialog. Of course it requires implementation of an additional protection method, but what's your point here with the dialog?
Created attachment 191998 [details] Cell Range Cell Range gets erased.
Yes unprotected cells need the protection as well, i don't know how to implement this. Right now if a user copies a cell from one part of the sheet into another the whole line gets removed from the range of the CF. If there would be a way to to lock/keep the range no matter if a user copies a cell that would be nice. Don't know if this is even possible. I have 12 CFs 1 for each month as long as user copy/paste within 1 month this is no problem but if they start to copy cells from january to febuary it messes up. Maybe my calendar just sucks. Maybe it is possible to put all months into 1 CF but there has to be a reason why i didn't do that in the first place :D If it helps i can attach the sheet, but its all german :/
(In reply to phantomlord2 from comment #16) > Yes unprotected cells need the protection as well... I believe protection on unprotected cell is not an easy-to-understand solution. That's why I advertise the opposite way namely to "weaken" (rather fine-tuning) the protection by allowing editing and formatting, or not. (In reply to Eike Rathke from comment #14) > And if unchecked, why would it prevent unprotected cells from being formatted? No change at all for unprotected sheets. I'm open for any rephrasing but adding another protection method makes everything much more difficult.
We discussed the topic in the design meeting. Additional to the idea of a) a special command to block (any) formatting in the spreadsheet (likely per table) or b) an addition to the protection mechanism to provide, we had the idea c) to remember the paste special settings (probably per document in this case). This could be done in the paste special dialog with a checkbox "[ ] Remember for paste" and the tooltip "Check this option to keep these settings for ordinary paste". The exact wording and implementation is open for discussion since the option "Run immediately" kind of blocks the "Remember" function. Of course, it would be possible to revert this by checking "All" and then "Remember" again (we should consider a preset for All). Option b) and c) sound quite flexible and by that appropriate to LibreOffice. But a) could be a solution for the issue too. I'm going to change the summary for all formatting since a cell might be directly formatted as result (for example large size and bold) but users cannot spot this formatting and overwrite it on paste.
sounds promising from my perspective, much appreciated.
Hi, a - this seems to be on _conditional_ formatting? b - I can preserve formatting (including conditional) of cells that I paste to very simple with Past Special. Or do I misunderstand this..?
> Actual Results: > the format of the conditional Formatting gets overwritten and the range is > automatically changed ("it removes the whole line from conditional formatting") Not able to reproduce the "quoted part".
(In reply to Printf Debugging from comment #21) > > Actual Results: > > the format of the conditional Formatting gets overwritten and the range is > > automatically changed ("it removes the whole line from conditional formatting") > > Not able to reproduce the "quoted part". Able to reproduce, but I think it was fixed somewhere. Now it just excludes the destination cell from the conditional formatting range, instead of the whole row of the destination cell. Explaination: 1. Select some rectangular range 2. Apply some conditional formatting on that range 3. Then go back (and see the conditional formatting reflected) 4. Reselect the same range and open conditional formatting dialog again -> No changes in the cond. formatting range(same single rectangle range A1:C14). 5. Then copy a cell with Ctrl-c and paste somewhere in the conditional formatting range with Ctrl-v 6. Open the conditional formatting dialog again (selecting the same range A1:C14 for eg), and you will find that this time that destination cell is excluded from the range. But as per the bug report, the whole row should have been excluded. So please confirm it the "whole line is exculded" issue is still there or not.
Created attachment 193843 [details] how it should be everything is fine
Created attachment 193844 [details] messes up after pasting the line of the target cf gets excluded cause i copy one cf into another. the calendar has 12 CFs. As long as the employees copy within a month everything is fine, but copying from 1 month to another will result in overwriting the cf and excluding the line from the range.
i have 23 employees in my job, all women, all are very nice but i'd rather jump repeatedly onto a bicycle without a saddle than explain them how to use shift+ctrl+alt+v :D
(In reply to phantomlord2 from comment #25) > i have 23 employees in my job, all women, all are very nice but i'd rather > jump repeatedly onto a bicycle without a saddle than explain them how to use > shift+ctrl+alt+v :D Create some macro, a toolbar with a nice colorful button that they love to use :)
Many functions are disabled by the admins for security reasons. Not sure if macros even work.
To sum up: a) copy/paste overwrites and uses the formatting (incl. CF) of the source b) copy/paste unformatted keeps formatting of target => no problem here * regular users are not aware of formatting and mistakenly do a) * potential solutions 1) add an option "[ ] Lock all formatting even on unprotected cells" per sheet/document (see comment 10) 2) add an option "[x] Protect formatting" to the sheet protection, which then needs to be accompanied by "[ ] Allow cell editing" (comment 6) 3) kind of variation to 1) switch the default of copy/paste optionally so copy/paste could be unformatted (comment 18) Eike's solution 2) is probably the easiest to implement.
Then its fine for me too. Thank you.