0

So here's what I need: when I click on certain cells in a spreadsheet in LibreOffice Calc, the background color changes to black. If I click again, it changes back to white. At first, all relevant cells are white. When document is saved, it should save the current condition so it persists when I reopen.

I've seen how to create a macro, but I don't know the coding language used here. I'm not completely incapable of programming (I work with php), but I'm not familiar with this.

I even found two close solutions on the web, but:

Change color of cell with mouse click in Excel This is actually for Excel, and unfortunately because they went straight to the solution with little explaining, I don't know what each line does and couldn't modify it.

https://forum.openoffice.org/en/forum/viewtopic.php?f=9&t=46389#p214370 This document has the solution with a different criteria (it changes cells to many different colors in an array, and counts clicks so it changes again after a few), but I couldn't figure it out because it's all commented in russian, and my russian is a little rusty... if I knew what part of the code determines which are the right cells to change and toggle with each click instead of counting several clicks, I'd have a solution I suppose.

I'm trying to design a simple character sheet for the Vampire the Requiem game on Calc, in a way that it's light, fast and easy to fill. World of Darkness traits come in dots, so to make it more easily fillable, I'd click cells instead. At least that's my idea. I suppose it could be done with PDF forms but I don't know how to work with those either...

Thanks in advance.

Community
  • 1
  • 1
RafaelLVX
  • 101
  • 1
  • 3

1 Answers1

1

You can write macros for LibreOffice in either LibreOffice Basic, BeanShell, JavaScript or Python, but, unless you've installed (or are willing to install) a Java Runtime Environment, you're stuck with LibreOffice Basic. Whatever programming language you choose, I suggest you tag your question accordingly so as to reach more people.

Anyway, if you're willing to try your hand at LibreOffice Basic, you can write something on the lines of:

Sub ChangeCellColor
    myCell = ThisComponent.CurrentController.Selection
    If {your condition here} Then
        If myCell.CellBackColor = 0 Then
            myCell.CellBackColor = -1
        Else
            myCell.CellBackColor = 0
        End If
    End If
End Sub

If you want the macro to affect any cell you select, just ignore the "If {your condition here}" bit. If you want it to affect only certain cells, though, define the condition accordingly. For example:

myCell.CellAddress.Row = 9

Macro only affects cells in row 10 (row 1 of the sheet has CellAddress.Row = 0)

myCell.CellAddress.Column = 4

Macro only affects cells in column E (same as above: column A has CellAddress.Column = 0)

Note that I'm assuming all your cells are either black or white, thus my "if it's black, make it white; otherwise, make it black" approach.

By the way, I tried to assign this to the Selection Change event of my sheet and it was a mess, so I assigned it to the Double-click event instead.

Raquel Peres da Silva
  • 250
  • 2
  • 10
  • That's a start, after adding this macro to my document, because I didn't know even how to assign this to events, I searched and learned it could be done by right clicking the spreadsheet name, selecting "Spreadsheet events" or similar (mine is in portuguese) and then assigning the macro to double-clicks. As you said, assigning it to simple cell selection didn't work as expected. Now on to the last part of what I needed to do, can I assign this only to certain cells, or in some way write the macro so that it only affects the right cells from an array perhaps? – RafaelLVX Sep 16 '15 at 19:10
  • You can't assign a macro only to certain cells, but you can have the macro check the cell address and only affect the cells you want. I edited my answer to show you how. – Raquel Peres da Silva Sep 16 '15 at 22:25