8

I am using LibreOffice 3.5.4.2. I would like to change the background color of cells based on various conditions. As a minimal example, I have the following macro/function defined:

function bgcolor()
Dim Doc As Object
Dim Sheet As Object
Dim Cell As Object   

Doc = ThisComponent
Sheet = Doc.Sheets(1)

Cell = Sheet.getCellByPosition(0, 0)
REM Cell.CellBackColor = RGB(50,60,70)
bgcolor=Cell.CellBackColor
end function

I execute the function by entering =BGCOLOR() into a cell. The cell in which that formula is present returns the color value of the first cell (0,0) or A1 on sheet 1, as expected.

However, I cannot get the function to change the background color of cell A1. The cell background color does not change when I remove the REM line in the example above to set the background color.

How can I set the background color of a cell with a function in LibreOffice?

(I read about using "styles", but did not look further at this because I need to set many different background colors and did not want to make many different styles. It is possible to manually change the background color without using styles, so I thought it would be possible to do the same programmatically.)

MPelletier
  • 16,256
  • 15
  • 86
  • 137
SabreWolfy
  • 5,392
  • 11
  • 50
  • 73
  • I do have the same problem: I set the cell's `CellBackColor` but nothing happens in the sheet. Did you solve your problem eventually? Any ideas? – Campa Feb 20 '15 at 13:45

3 Answers3

4

First, there is nothing wrong with your macro in general. The single line to set the CellBackColor is correct as stated. The problem is that the function is called from the sheet that you are attempting to modify. A function is not allowed to modify the sheet from which it is called. So, if you call your function from sheet 1 and then try to change the background color of a cell in sheet 1, that will fail. If, however, you attempted to change the background color of a cell on sheet 0 when calling from sheet 1, that will work as expected.

Andrew
  • 816
  • 7
  • 15
  • Thanks. Please edit your answer to include a source/citation for future reference? – SabreWolfy Oct 29 '15 at 13:14
  • 2
    From https://wiki.openoffice.org/wiki/Documentation/OOo3_User_Guides/Calc_Guide/Accessing_cells_directly: "When a macro is called as a Calc function, the macro cannot modify any value in the sheet from which the macro was called." – Jim K Nov 08 '17 at 18:14
  • 1
    However, in newer versions, it is possible to modify cell values but not cell attributes on the same sheet. – Jim K Nov 09 '17 at 20:01
2

the line should be

cell.cellbackcolor = RGB(50,60,70)

(no "REM" there of course, that creates just commented line)

consider also the parameter of sheets to be 0 instead of 1 if you have only one sheet

for other interesting properties, see cell properties

Micky
  • 21
  • 3
0
Cell.BackColor = RGB(50,60,70) should do the trick

edit: This works only in lowriter. Thanks Campa.

bdongus
  • 658
  • 4
  • 20
  • I'm not sure if you suggest replacing the `REM` line in my example with your line, or if your line is a separate example. I tried both approaches, but this has not solved the problem. If I replace the line in my example with yours, the cell returns `-1`. – SabreWolfy Jan 04 '13 at 12:41
  • I suggested to replace the REM line. But I don't know what Konrad edited. Maybe it is not readable. Would fit to some weird logic in LO. :) http://api.libreoffice.org/docs/common/ref/com/sun/star/table/CellProperties.html#CellBackColor – bdongus Jan 28 '13 at 17:10
  • Checked my own macro for tables in lowriter. Should work in localc too. ` For nRow = 0 To oTable.getRows().getCount() - 2 with oTable.getrows().getByIndex(nRow) If nRow MOD 2 = 1 Then .BackColor = -1 Else .BackColor = RGB(192, 192, 192) End If end with ... Next` – bdongus Jan 28 '13 at 17:18
  • @bdongus : Your proposed solution gives `BASIC Runtime error. Object variable not set` ERROR. – Campa Feb 20 '15 at 13:44
  • @Campa: Can you please post your code? Otherwise I can only guess: You used the loop without setting oTable? – bdongus Feb 21 '15 at 17:04
  • @bdongus Hey: I just get my cell using `Sheet.getCellByPosition` function, I don't use table objects. Then `myCell.CellBackColor = RGB(255, 0, 0)`. I mean, exactly as SabreWolfy did. – Campa Feb 23 '15 at 07:53
  • @Campa: Which Object is is undefined. If it is CellBackColor there might be a bug in calc. If not, maybe this helps: [link](http://pastebin.com/PSxNS27n) – bdongus Feb 25 '15 at 19:49
  • @bdongus: no, my previous comment refers to your `Cell.BackColor` statement which you should correct in your answer to `Cell.CellBackColor` in case it is a typo. In my case, everything compiles fine, but simply nothing happens on the sheet (exactly like @SabreWolfy's situation). Using `.CurrentSelection` as in the link you gave to me, throws `com.sun.star.lang.IndexOutOfBoundsException` on `getCellByPosition`. – Campa Feb 26 '15 at 07:16