I just got my e-mail back from the OOo list. What a shambles.
It looked all right when I composed it.I suppose I'll have to take a
course composing e-mails. So here it goes again.
Niklas Nebel wrote:
Sathya C wrote:
Is there a way to know which cell in the range was modified?
If you add the listener to a cell range or sheet, no, and you'll also
be notified only once if several cells are modified at the same time.
If you want to observe individual cells (and there's not too many of
them), you can try adding a listener to each cell.
Niklas
Sathya, Niklas,
1. This reaction is late, but I hope it's not too late. The demo I
tried to write caused me some trouble,and then I didn't have time and
so on.
2. Niklas is right where he says "and there's not too many of them".
For a cell range of say 10 columns by 20 rows you would need 200
listeners. You could reduce this number by attaching a listener to every
column and every row in the range, resulting in 10 + 20 = 30 listeners.
When one cell is modified two listeners will provide respectively their
column and row number and you'll have a position. When several cells
are modified at the same time you'll get a lot of calls from a bunch of
listeners.
3. A workable solution with only one listener for the whole range
might be the use of two arrays for comparison and two others to store
the result. The first one should contain the data in the range before
modification and the second the data after modification. The listener
should trigger copying of the modified data to the second array.
Then compare both arrays row by row, cell by cell, and only when there
is a difference store column and row numbers respectively in two one
dimensional arrays. These two arrays then contain the coordinates of
your modified cells. (Thanks to Andrew Pitonyak for pointing out the use
of arrays if you want speed.) For this to work the first thing you'll
want to do when you open the document is load the current contents of
the range into the first array, so you should attach that action to the
"Open Document" event using Tools-Customize-Events. At the same time
install the listener, so I'ld combine that in one subroutine. After that
every time you have done whatever you want to do with the modified
cells, you must copy the then current contents of the range to the first
array to be ready for a next comparison.
4. The trouble I mentioned above was that sometimes the first array
lost its data and turned up empty (do you use Bernard Marcelly's
XrayTool? It's great!). I had no idea what caused this, and actually I
still am not sure. Today I have been tinkering with Dim and Redim of
the arrays and I think it's doing what it should now. But don't pin me
down on it, please. Maybe someone on the list sees weak spots.
The demo code to watch a range called "chckRng" looks like this:
Global oDocument As Object
Global oDocSecond As Object, oSheet As Object, oCompSheet As Object,_
oRange As Object, oCell As Object
Global sSheetName$, sRangeName$, sChartName As String
Global mRngWas() As Double, mRngIs() As Double 'Arrays
Global mCols() As Integer, mRows() As Integer 'Arrays
Global i%, j%, k As Integer
Sub setupOpenDoc 'Attach this to the "Open Document" event.
Call uCrngListener
Call getRngWas
End Sub
Sub uCrngListener
oDocument = ThisComponent
oCrng = oDocument.Sheets.getByName( "Sheet1" ). _
GetCellRangeByName( "chckRng" )
oCrngData = CreateUnoListener( "CrngListener_", _
"com.sun.star.util.XModifyListener" )
oCrng.addModifyListener( oCrngData )
End Sub
Sub CrngListener_modified( oEvent )
Call compareArrays
End Sub
Sub CrngListener_queryInterface( oEvent )
' MsgBox "queryInterface" 'Sub must exist
End Sub
Sub CrngListener_disposing( oEvent )
' MsgBox "disposing" 'Sub must exist
End Sub
Sub getRngWas 'Gets old = current data
oDocument = ThisComponent
oSheet = oDocument.Sheets.getByName( "Sheet1" )
oRange = oSheet.GetCellRangeByName("chckRng")
mRngWas = oRange.getDataArray()
End Sub
Sub getRngIs 'Gets modified data.
oDocument = ThisComponent
oSheet = oDocument.Sheets.getByName( "Sheet1" )
oRange = oSheet.GetCellRangeByName( "chckRng" )
mRngIs = oRange.getDataArray()
End Sub
Sub compareArrays
Call getRngIs
k = 0
ReDim mCols( k )
ReDim mRows( k )
For i = LBound( mRngIs ) To UBound( mRngIs )
oRowIs = mRngIs( i )
oRowWas = mRngWas( i )
For j = LBound( oRowIs ) To UBound( oRowIs )
if oRowIs( j ) <> oRowWas( j ) then
mCols( k ) = j
mRows( k ) = i
k = k + 1
ReDim Preserve mCols( k )
ReDim Preserve mRows( k )
end if
Next j
Next i
Call getRngWas 'store new contents as "was"
Call showDiff
End Sub
Function showDiff
'xray mCols()
'xray mRows()
For m = 0 to k - 1
MsgBox "col = " & mCols( m ) & " row = " & mRows( m )
Next m
End Function
I hope this helps,
Sincerely,
Gerrit Jasper
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
