date of change in a row

[Gogol]

Hi,
I have a Calc spreadsheet with about 2000 rows, more will be added. Each row coresponds to one project (several parameters of the project are recorded). One of the columns holds the date of last change in project parameters. People always forget to update this cell and I thought a macro could do it for them.
I know how to add an event listener to the cells in each row and how to write the current date into the specific cell. But I don't want to have thousands of subroutines (one for each row). Is it possible to have one subroutine for all event listeners and give it the row number as a parameter? Or maybe a function returning position of the last changed cell?
Thank you,
Gogol

[ms777]

I believe, one event listener for the whole sheet should be sufficient. With ThisComponent.CurrentController.CurrentSelection, you should be able to identify the modified cell

Good luck,

ms777

[Gogol]

I'm sorry I didn't mention it, but I have already tried that with very little success. If I modify the cell and move to another one using left/right arrow (you stay on the same row) it works fine. But the event listener is way too slow and if I use the ENTER key after modification, the cell below becomes active before the subroutine is launched and the date is written in a wrong row.
Is there any other way?

Thank you,
Gogol

[ms777]

can you post the relevant part of your routines ?

[Gogol]

Hi,
here is the code that works but it isn't suitable for 10000 thousands of rows:

Sub SetListeners(x1,y1,x2,y2)
'--- installation of an event listener

'--- oGroupLinest is group of cells where LINEST is computed
'--- all change in this group will invoke OOO_chartDataChanged and then ComputeSubTitle
for y=y1 to y2 step 1
oListener = createUnoListener("OOO_"+y+"_","com.sun.star.chart.XChartDataChangeEventListener")
for x=x1 to x2 step 1
oCheckCells=oSheet.getCellByPosition(x,y)
oCheckCells.addChartDataChangeEventListener(oListener)
next
next
End Sub

Sub OOO_0_chartDataChanged
OOO_chartDataChanged(0)
End Sub

Sub OOO_1_chartDataChanged
OOO_chartDataChanged(1)
End Sub

Sub OOO_2_chartDataChanged
OOO_chartDataChanged(2)
End Sub

Sub OOO_3_chartDataChanged
OOO_chartDataChanged(3)
End Sub

Sub OOO_4_chartDataChanged
OOO_chartDataChanged(4)
End Sub

Sub OOO_5_chartDataChanged
OOO_chartDataChanged(5)
End Sub

Sub OOO_6_chartDataChanged
OOO_chartDataChanged(6)
End Sub

Sub OOO_7_chartDataChanged(row)
OOO_chartDataChanged(7)
End Sub

Sub OOO_8_chartDataChanged
OOO_chartDataChanged(
End Sub

Sub OOO_9_chartDataChanged
OOO_chartDataChanged(9)
End Sub

Sub OOO_10_chartDataChanged
OOO_chartDataChanged(10)
End Sub

Sub OOO_chartDataChanged(row)
oDateCell=oSheet.getCellByPosition(7,row)
oDateCell.SetString(Now)
End Sub


And here is the code that would be nice but sometimes writes the date into wrong row (when you hit enter a nd the cell below becomes active):

Sub SetListeners(x1,x2)
for x=x1 to x2 step 1
oListener = createUnoListener("OOO_","com.sun.star.chart.XChartDataChangeEventListener")
oCheckCells=oSheet.Columns(x)
oCheckCells.addChartDataChangeEventListener(oListener)
next
End Sub

Sub OOO_chartDataChanged
Row = ThisComponent.CurrentSelection.RangeAddress.StartRow
oDateCell=oSheet.getCellByPosition(7, Row)
oDateCell.SetString(Now)
End Sub

Thank you very much for your help,
Gogol

[ms777]

Hi,

I first thought that examining the object being passed to OOO_chartDataChanged would deliver the row/column, at least according to the docs it should. But unfortunately, it alwas passes 0 for row and col. So I inserted a SelectionChangeListener. The below codes still needs some modification for setting the global variables when initializing. But after one or two data entrie into the sheet it works ...

Good luck,

ms777