Changing cell background color in LibreOffice

Question

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

Solution 1

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.

Solution 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

Comments

[SabreWolfy]

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.

[bdongus]

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. :) api.libreoffice.org/docs/common/ref/com/sun/star/table/…

[bdongus]

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`

[Campa]

@bdongus : Your proposed solution gives BASIC Runtime error. Object variable not set ERROR.

[bdongus]

@Campa: Can you please post your code? Otherwise I can only guess: You used the loop without setting oTable?

[Campa]

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

[bdongus]

@Campa: Which Object is is undefined. If it is CellBackColor there might be a bug in calc. If not, maybe this helps: link

[Campa]

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

[SabreWolfy]

Thanks. Please edit your answer to include a source/citation for future reference?

[Jim K]

From wiki.openoffice.org/wiki/Documentation/OOo3_User_Guides/…: "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]

However, in newer versions, it is possible to modify cell values but not cell attributes on the same sheet.