Auto change cell format "on update" event

[rolf]

I’m looking for a possibility to have Calc automatically trigger an event or function, like f.e. change a cells format, once the cell content has been updated / edited.

The reason I’m not sticking with the “track changes” function is that I have to later work with the cell contents of those cells that have been edited, and the formatting marks of this function are very limited.

It does not even have to be a change of cell format, it could be sth. like insert a timestamp of the Edit/Update event into a cell after the edited cell in the same row.

I’d be fine with conditional formatting, or macros, only I can’t seem to find something like a “onEdit” or “onUpdate” event, which is what I’m basically looking for.

Thanks in advance for any suggestion!

[Lupp]

Please note that a script cannot ask a cell for the format (attributes) applied to it for the view by ConditionalFormatting.
As generally formulas are not aware of any history(¹) (previous states) they also are if used in CF.
Also if you use the ‘Content changed’ event (see below), you only have access to the ContentAfter and never to the previous content - except you explicitly saved it elsewhere precautionary.
(¹) The only rather fragile restriction is the access to the previous-current value for a reference during recalculation under iteration.

[Lupp]

I don’t feel sure about your actual needs. The question as I understand it mixes up changes in formats, contents - and probably even formula results and or cell dimensions.

The mentioned cases need different treatment, and only for the editing of contents there is a simple solution: Every sheet raises a respective event if the content of at least one cell was edited. You can assign your routine (script) to the dialog’s list item ‘Content changed’ accessible via the sheet-tab’s context menu item ‘Sheet Events…’.

Please note:
-1- Despite its name the event is also risen if the content was edited, but in the end set unchanged. Even using ‘Del’ on a blank cell raises the event. The event is not risen if an entered process of editing was cancelled (‘Esc’).
-2- The called routine gets passed one parameter (say pEvent) telling you what cells were edited.
-3- The value of pEvent is an object that can be of two different types: SheetCellRange or SheetCellRanges. The case of a single cell is subsumed under the first case. The cases must be distinguished by your code.

To listen to any different kind of changing cell properties - if supported at all - will need to explicitly register a listener. I doubt if you will get a respective tutorial in a forum. You will need to study “the literatur”, I’m afraid. Be prepared to experience problems on your pursuit of information.
Example: LibreOffice: XModifyListener Interface Reference

===Edit1 2019-03-24 18:31 UTC===
With respect to my comment to the answer by the OQ I post the following code.

Sub markiereBearbeitet(oEvent)
REM Wolfgang Jäger 2019-03-25
oDoc              = ThisComponent
oSheet = oEvent.Spreadsheet
If Not oEvent.supportsService("com.sun.star.sheet.SheetCell") Then Exit Sub
If NOT (oEvent.CellStyle="csForEditing") Then Exit Sub
oEvent.CellStyle  ="csEdited"
With oEvent.CellAddress
  oNeighbour        = oSheet.getCellByPosition(.Column + 1, .Row)
End With
oNeighbour.String = "bearbeitet"
End Sub  

To get it work as intended you need to create the cell styles “csForEditing” (which may otherwise be identiacal to “Default”) and “csEdited” (“bearbeitet”), and to apply “csForEditing” to all the cells you want to apply the Sub when edited later. This way you may edit other cells without triggering the “bearbeitet” effacts.
Having finished you can filter the rows containing the keyword in the cell right next to the edited cells.

[rolf]

Thank you very much. The “Sheet Events” with its “Content changed” trigger was what I was looking for (and what I was not aware of).

I ended up assigning a cell style for edited cells as follows. That’s enough for my use case.

Sub Main
Dim ODoc As Object
Dim OCell As Object
oDoc = ThisComponent
oCell = oDoc.GetCurrentSelection()
If Not oCell.supportsService("com.sun.star.sheet.SheetCellRange") Then Exit Sub
If (oCell.Columns().Count() > 1) Then Exit Sub
If (oCell.Rows().Count() > 1) Then Exit Sub

oCell.CellStyle="bearbeitet"
End Sub

This has been assigned to Sheet Events / Contents changed; it does not include a case of SheetCellRanges, but it does its job as I need it right now.

Thanks again!

[Lupp]

If Not oCell.supportsService("com.sun.star.sheet.SheetCellRange") Then Exit Sub
Is this actually what you want if you pasted new content into a range at a time?
I would not expect every editing to be done via the keyboard one cell by one.

[rolf]

In my case, it’s exactly what I want. Background: I’m translating strings from a bunch of them where not every string actually needs to be translated, and in the end I want to sum up the amount of text that I did translate (and have it, as a side effect, visually accentuated).

So, it’s really one cell by one via keyboard. In the end, I’m just going to mark + copy all cells that have the “bearbeitet” style applied.

[Lupp]

If you want to “mark & copy” finally", a preliminary marking using a cell style only is not the best choice because you cannot directly filter based on it. Anyway, the “bearbeitet” is expected to select rows, isn’t it? Therefoore it is used as data and should be representede by real data, best in the respective cell of an adjacent column. If the column is available, it’s very simple to do so in addition from the Sub you already have. See amendment to my answer.

[rolf]

Thanks Lupp. Got back to it now after having implemented & tested your suggestion with a few minor tweaks. Works great and is exactly what I was looking for, and comfortable to filter.