Description: We are volunteers transcribing historic weather data for use in building a climatology over the oceans. We do this by having a scanned log page from a U.S. Navy ship as a background in a LibreCalc spreadsheet. The cells in the spreadsheet line up with the data grid on the log page, and we enter the data directly over the value showing on the image. These logs are very old, and sometimes the writing is very faint which necessitating changing the image to a greyscale with enhanced gamma and contrast values. By doing so, our normal font colours also have to be changed. They also have to be changed by one of our transcribers who is colorblind. The best font colour for comparing the data entered with the underlying image data is yellow. However, it is nearly impossible to see the yellow values when entering the data. You can, of course, see the value being entered in the formula box at the top left of the spreadsheet, but it is very hard on the eyes to be switching from one part of the spreadsheet and back, over and over again. The background colour of the cell, when entering data, is white, and set by the Options - Libre Office - Application Colors - Document Background. If we change this colour, it affects other sheets in our spreadsheet, and other applications of Libre Office. It would be very beneficial if the background colour of the cell when entering data could be set with its own variable. It would save us a lot of eyestrain. Steps to Reproduce: 1.While entering data in a cell note the colour. 2. 3. Actual Results: The colour is set by Options - Libre Office - Application Colors - Document Background. Expected Results: It would be wonderful to be able to set this colour separately from the Document Background. Perhaps something like; Options - Libre Office - Application Colors - Active Cell Background Reproducible: Always User Profile Reset: No Additional Info: It would be wonderful if, at some time, you could add an option like: Options - Libre Office - Application Colors - Active Cell Background Thank you.
Created attachment 196362 [details] This shows the use of a yellow font on an enhance image. The yellow does not show up when entering data with the Document Background set to Automatic. This shows the use of a yellow font on an enhance image. The yellow does not show up when entering data with the Document Background set to Automatic. Changing the Document Background causes problems in other parts of the spreadsheet, which has a total of ten sheets (or tabs). The value being entered can be seen in the formula box at the top left of the spreadsheet, but continually scanning back and forth or straining to read the value in the cell being entered causes eyestrain.
If I may... Since you need clear contrast against the background color of the cell in both cases (while editing, and after the value has been completely introduced), isn't it simpler to just change the font color of the whole spreadsheet? Is there any reason for the spreadsheet to use a yellow font? Can't you change it to, say, "Light Blue 1" (or anything else other than yellow, which is not so readable unless you use Dark Mode / Dark Themes)?
As Ady said, I guess it would be simpler to select the cells behind the image and change their background color to something darker (maybe black). Then when you edit the cell, you'll se a black background.
(In reply to Rafael Lima from comment #3) > As Ady said, I guess it would be simpler to select the cells behind the > image and change their background color to something darker (maybe black). > Then when you edit the cell, you'll se a black background. Just for clarification, I meant change it to black while editing, and then remove the background color after you've finished.
Re-reading my own comment 2, I realize I was not clear. What I am trying to ask is whether there is, perhaps, some font color that would be usable for both statuses (background image, and edit mode). I cannot suggest a specific font color, considering that the specific color-blindness might distort whatever anyone else could suggest. Setting such font color (if it were to exist) could be an alternative to this RFE, I guess. Other than changing the application background – which you already mentioned, as not appropriate – there is also the "Format > (Spreadsheet) Theme" feature, or (for the purpose of this discussion), changing the background color of all cells. The resulting background color of cells while editing is then changed, but (in terms of the issue in this RFE) this is equivalent to using a background color on cells, which blocks your scanned background image. I don't know whether there is a way to set the background image in some layer that would allow to set a background color to cells (so the color can be used while editing the cell), but that would still show the background image while the cell is not being edited – I am simply not used to using background images in spreadsheets in this way. All of the above are attempts to suggest potential alternatives (which I am less familiar with). CC'ing Michael Weghorn for accessibility.
(In reply to Rafael Lima from comment #4) > Just for clarification, I meant change it to black while editing, and then > remove the background color after you've finished. I think that is not applicable for the use-case. Setting a background color (I guess that with no transparency) would block the background image, which is needed in order to re-type the values in it. Going back and forth every time is not applicable either, because it would be simpler to just look at the formula bar input window box, which is already reported as "eyestrain" in comment 0. So, the possible alternatives are: * to be able to set a background color that somehow (transparency? front/back layer?) would not block the background image (except while editing the cell); or, * to find a font color that would work for both background colors, white color while editing and gray-ish otherwise. I also guess that changing the whole Theme (say, to some Dark Mode, or whatever else) has the same limitations/conflicts as changing the application color, as described in comment 0.
(In reply to ady from comment #2) > If I may... Since you need clear contrast against the background color of > the cell in both cases (while editing, and after the value has been > completely introduced), isn't it simpler to just change the font color of > the whole spreadsheet? > > Is there any reason for the spreadsheet to use a yellow font? Can't you > change it to, say, "Light Blue 1" (or anything else other than yellow, which > is not so readable unless you use Dark Mode / Dark Themes)? Generally speaking, we use a red font. For this particular set of pages, yellow stands out best. The ink is so faint that it is most easily readable when the image is changed from its natural colour to a grey scale and with the contrast and gamma variables adjusted for the best result. When we do that, the red font does not stand out as well as the yellow. We have been using LibreCalc for this project for about seven years. We have tried different themes, and different font colours, none of which work very well for every log page. The scanned log pages vary in colour from white to a yellowish colour. Generally they were filled in with black ink, but sometimes with blue ink or with blue or red pencil. We work with logs generally from the 1860s to 1880s.
Created attachment 196370 [details] This is the actual spreadsheet with the ehanced image and the yellow font. This is the actual spreadsheet. You can see how well the yellow font stands out against the background. Generally speaking, a red font is the best choice for most spreadsheets, but not in all cases.
Created attachment 196371 [details] Here is the actual downloaded image. This is the actual image. After importing the image, I changed the enhancements to make the writing more readable. I found that keeping our standard red font meant I had to strain to be able to proofread my entries. A yellow font made it much easier. Different people have tried different combinations. It depends on the image. To date, we have extracted data from over 75,000 images, so you can appreciate that no one option works in all cases. One of our transcribers cannot see blue. I do not know the inner workings of the spreadsheet, but I was hoping that the edit mode colour could have its own variable different from Options - LibreOffice - Application Colors - Document Background. Were that the case, I could set the Edit mode colour to be something like Light Grey 2 for this set of images.
Seeing tdf#142679: Would this request here still be relevant if that one were implemented? (In reply to Michael from comment #0) > The background colour of the cell, when entering data, is white, and set by > the Options - Libre Office - Application Colors - Document Background. If we > change this colour, it affects other sheets in our spreadsheet, and other > applications of Libre Office. Wouldn't the same be true (at least for other sheets) when making the background color for the currently edited cell configurable? (Couldn't that create similar problems in theory at least?) Since switching the document background color in the settings achieves the desired effect, could making that even easier for users maybe be an option? (E.g. by setting up 2 LO profiles where one has the specific document background color set and is used for transcribing, while the other profile uses the default. Or maybe switching the document background could even be automated using a macro. [1] might be helpful then, but I don't have any own experience with that myself.)
I tried to record a macro to change the Background Macro, but the recording gave me nothing other than this: sub Main rem ---------------------------------------------------------------------- rem define variables dim document as object dim dispatcher as object rem ---------------------------------------------------------------------- rem get access to the document document = ThisComponent.CurrentController.Frame dispatcher = createUnoService("com.sun.star.frame.DispatchHelper") rem ---------------------------------------------------------------------- rem dispatcher.executeDispatch(document, ".uno:OptionsTreeDialog", "", 0, Array()) end sub I also hunted around with Google and I looked in Andrew Pitonyak's books. All with no success. For now, we just switch back and forth setting the option as required. It's not a huge deal. It just occurred to me: the easiest solution for us would be if there was an icon on the Formatting Toolbar, like the Font and Cell Background Icons that would change the Document Background colour. If that were possible, it would be best, and it wouldn't necessitate adding another variable to the Application Colors.
(In reply to Michael from comment #11) > I tried to record a macro to change the Background Macro, but the recording > gave me nothing other than this: A macro to change the background color of the active sheet would be: Sub ChangeBgColor Dim oSheet As Object oSheet = ThisComponent.CurrentController.ActiveSheet ' Put whatever color you want here oSheet.CellBackColor = RGB(50, 50, 50) End Sub One can create this macro and associate it with a button in the toolbar. > and it wouldn't necessitate adding another variable to the > Application Colors. I am hesitant to add new color option to the App Colors dialog as well.
Changing the CellBackColor is not the solution. If I do that, I can't see the image, In fact, everything in the sheet goes black. I need to change the Document Background Color. So, I need something like this: Sub ChangeBgColor Dim oDoc As Object oDoc = ThisComponent ' Put whatever color you want here oDoc.BackColor = RGB(50, 50, 50) End Sub I have tried different combinations like BackColor, DocBackColor, DocumentBackColor etc with no luck. I would also have to know how to add an item to the Formatting toolbar. I might be able to find that out tomorrow, but it is very late here, and I have been out all afternoon. Thanks anyway for the suggested macro. It will put me on the right path.
Okay... if you want to change the document background color, here's the macro: Sub ChangeDocBgColor GlobalScope.BasicLibraries.LoadLibrary("Tools") Dim keyNode As Object ' com.sun.star.configuration.Configuration(Update)Access ' ' Get current color scheme keyNode = Tools.Misc.GetRegistryKeyContent("org.openoffice.Office.UI/ColorScheme", False) curScheme = keyNode.CurrentColorScheme ' Get the scheme node nodeName = "org.openoffice.Office.UI/ColorScheme/ColorSchemes/" & curScheme & "/DocColor" ' Set the root path for our configuration access keyNodeColor = Tools.Misc.GetRegistryKeyContent(nodeName, True) ' Replace RGB values below keyNodeColor.setPropertyValue("Color", rgb(150, 150, 150)) keyNodeColor.commitChanges() End Sub Note however that this will change the Document Background color for all applications.
(In reply to Rafael Lima from comment #14) > Okay... if you want to change the document background color, here's the > macro: > > [...] Awesome, thanks a lot! (In reply to Michael from comment #13) > I would also have to know how to add an item to the Formatting toolbar. That's possible via "Tools" -> "Customize", see https://ask.libreoffice.org/t/assign-macro-to-toolbar/33575 . (In reply to Michael from comment #11) > It just occurred to me: the easiest solution for us would be if there was an > icon on the Formatting Toolbar, like the Font and Cell Background Icons that > would change the Document Background colour. If that were possible, it would > be best, and it wouldn't necessitate adding another variable to the > Application Colors. Can we close this bug as WONTFIX then, because the use case can be covered by the toolbar item that runs the macro instead?
I'm almost there! I got the macro running. (I had to copy it with a Shift-Insert command, not a Ctrl-V, in order to it to paste properly.) Next, I added it to the Formatting Toolbar. (See attached image.) However, a perfect solution would be to have a an item that was a clone of the Font Color item, but it would change the Document Background Color. The Font Color item is wonderful because the user can pick the color they need. I can modify the ChgDocBgColor to bring up an input box and give the user a selection of a few color choices, but it would not be as elegant as having an item like the one for Font Color.
Created attachment 196399 [details] This shows the addition of the macro to the Formatting Toolbar. A perfect solution would be to have an item like the Font Color item, but which would allow the user to choose a color for the Document Background. If I can get that, this enhancement request can be closed as solved.
(In reply to Michael from comment #17) > A perfect solution would be to have an item like the Font Color item, but > which would allow the user to choose a color for the Document Background. That basically means to duplicate the current: Tools > Options > LibreOffice > Application Colors > (General) Document Background ...to some UNO command so it can be used for toolbar customization. FWIW... wouldn't be better to actually solve this by a real enhancement (as originally requested, to be able to set a background color for a cell while using cell's edit mode)? An alternative enhancement could be to allow the possibility to have the background image to be on a "third" background layer, behind the cell's (not document) background color while the cell is in edit mode. Currently (but I could be wrong), the background image is _always_ behind the cell's background color. This means that we effectively use either a BG color or a BG image (as combining them is not usable in practice). If the foreground layers were to be configurable (A: while in cell's edit mode, and B: while _not_ in cell's edit mode), then several of the OP (Michael's) requests would be solved for the use-case – see his other RFE – and both kinds of BGs would be simultaneously usable. That is, unless I am wrong and there is a way to set the order of these layers(?).
Unfortunately for me, I have no idea about the inner workings of LibreCalc, so I don't know what's practical and what is not. I very much appreciate all the work you guys do. One of the volunteers has upgraded to 24.8.0 so he can change the color of the Spreadsheet/Cell Focus Color which you recently implemented. It has made his work on the spreadsheet much easier. Thank you! 1. Originally, we requested that the background of the cell when in Edit Mode would be the image itself. That would be ideal. 2. Someone said Option 1 wasn't feasible, so the next best option would be to have a way to set the background colour of the cell when in Edit Mode by having a separate variable from Application Colors - Background Color. It would be best to be able to change the colour of the cell when in Edit mode and not affect the Document Background color, which leads to other issues. 3. Someone said Option 2 is not practical or feasible either, so the next best option would be to have an item on the Formatting Toolbar to change the Document Background colour. Having it there makes it easy to swap colours back and forth as required. 3a. Ideally, that would be an application that looks and works like Font Color. Perhaps someone on the development team could write application that looks and acts like Font Color that changes the Document Background. 3b. In the meantime, just having a macro would suffice. I tried a macro someone posted in an earlier comment. That macro runs when it is in: My Macros & Dialogs - Module 1. It does not work when it is a module for a specific spreadsheet - like our Extensions module. When I run that version of the macro, I get a Basic Runtime Error, Variable not defined for this line: curScheme = keyNode.CurrentColorScheme If I can get the macro from the Spreadsheet - Extension modules to work, then I can put it into all the current spreadsheets we're running, and the other volunteers will be able to use it. It won't be as elegant as Option 3a with an icon like the Font Color application and it won't have all the colour options, but I made an input box in the macro that allow the user to pick one of 12 colors. Perhaps in time there will be a proper application that we, and others, can have on one of their toolbars.
[Automated Action] NeedInfo-To-Unconfirmed
(In reply to Michael from comment #17) > A perfect solution would be to have an item like the Font Color item, but > which would allow the user to choose a color for the Document Background. > > If I can get that, this enhancement request can be closed as solved. Maybe using a control of type ColorListBox [1] might get a more similar behavior for the newly added toolbar item. If the "Tools" -> "Customize" dialog doesn't provide enough flexibility, I suspect that creating an extension would give more opportunities. (But I'm not very familiar with macro/extension development myself.) (In reply to ady from comment #18) > That basically means to duplicate the current: > Tools > Options > LibreOffice > Application Colors > (General) Document > Background > > ...to some UNO command so it can be used for toolbar customization. > > FWIW... wouldn't be better to actually solve this by a real enhancement (as > originally requested, to be able to set a background color for a cell while > using cell's edit mode)? Since this is a very special use case, a macro/extension seems to be a good solution to me, not changing LibreOffice core. [1] https://api.libreoffice.org/docs/idl/ref/namespacecom_1_1sun_1_1star_1_1inspection_1_1PropertyControlType.html#a18e7a20f78c79ee498295e3fe1a77b05
I couldn't figure out why the ChgDocBgColor macro ran from the My Macros & Dialogs Module 1 area, but not from My Spreadsheet/Extensions area. I realized this morning that all our modules run with Option Explicit. I had to explicitly declare the following three variables: Dim curScheme As String Dim NodeName As String Dim keyNodeColor As Object
I have a macro working the way we need, so you can consider this enhancement as resolved. As I noted, the perfect solution would be to have the image visible as you enter the data, but I understand that is not possible. This way is the next best solution. Thank you.
(In reply to Michael from comment #23) > I have a macro working the way we need, so you can consider this enhancement > as resolved. As I noted, the perfect solution would be to have the image > visible as you enter the data, but I understand that is not possible. This > way is the next best solution. Thank you for your feedback. IIUC, there's still tdf#142679 to track the possibility to see the image while editing data.
That would be ideal. What I've cobbled together works, but it is not elegant as an icon based tool to do the same thing. Mind you, I'm sure there aren't many other people doing work like we are, so it's not a big deal.