[Solved] [Calc] Listeners for cell modification events

[FreakWent]

Searching the forums, we can see that there's been a consistent problem for many new openoffice.org programmers detecting cell change events on a sheet.

People have struck this problem as far back as 2004 at least, searches on http://www.oooforum.org will find many examples. More relevantly, we see it onthis site only a few months ago.

That thread doesn't help me though, as I've got no idea what the OP means by "send out the entire row for every change" in his last comment there.

In any case, the scenario is that if you assign a listener to a range of cells (or even a sheet?), when you are writing the event handler you only have the range object to work with, and you don't know which cell was modified. From a lot of searching and reading, there are two basic approaches to work around this limitation, as far as I can tell. One is to duplicate the range in an array that you store in the macro, then every time a change is triggered:

- read the range into a new array
- iterate through one of the arrays, comparing each element until you find out which one changed
- disable the listener if you need to
- do whatever it is you actually wanted to do
- refresh your persistent array from the cell range in the spreadsheet
- enable the listener

The other approach is to establish an array of Listeners, and assign one listener to each cell in the range. Then, your handler will be passed a cell object, so all you have to do is:

- disable the listener if you need to
- do whatever it is you actually wanted to do
- enable the listener

In my opinion as an IT dude, both these approaches SUCK. However, I'm pragmatic, and I'll work with what we have. I like the second version much better, but searches here and over at the other forum haven't helped me much; I have looked in the snippets area also.

Can anyone please post me a basic code template that implements the array-of-listeners technique? If not, I'll do it myself anyway, but I have a feeling that there's an authoritative reference implementation that would be better than anything I could come up with.

If not, check back in a few days and I'll post my solution, because I've been messing about with this stuff for days now and it's doing my head in -- if I can save someone else from such a fate, so much the better!

Cheers!

[FreakWent]

Well, no responses so I tried anyway.

I cannot turn off the listeners!

Be careful, this code crashes office for me.

Look:

Code: Select all

Global oRange As Object
 Global oListener    As Object
 Global loopcop


Sub SetupIntegrity
	loopcop=0
    oScalcDocument=ThisComponent
    oSheet = oScalcDocument.Sheets(0)
    
    oRange = oSheet.getCellRangebyName("B9:F60")
    oListener = createUnoListener("MyApp_", "com.sun.star.util.XModifyListener")

	dim oCell as object
 
 	For row = 0 to 51
		 For col = 0 to 4
			oCell = oRange.getCellByPosition(col,row)
			oCell.SetString("Ready!")
			oCell.addModifyListener(oListener)
		Next col
	Next row
End Sub


'******************  The event handler keeps getting called, this code is broken, it loops forever and I had to kill openoffice. 

Sub MyApp_Modified (oEvent)



	dim oCell as object

'=============  Here you can see an attempt to shut off all the Listeners, the same way they were turned on 
'============= Uncomment this code if you like, it fills all the cells with "Off!" but the MyApp_Modified routine gets called all the time...

'	For row = 0 to 51
'		 For col = 0 to 4
'			oCell = oRange.getCellByPosition(col,row)
'			oCell.RemoveModifyListener(oListener)
'			oCell.SetString("Off!")
'		Next col
'	Next row


ChangedCell = oEvent.Source

userrow = ChangedCell.CellAddress.Row-8
usercol = ChangedCell.CellAddress.Column

If usercol=1 then
	yearly = ChangedCell.Value/7*365
elseif usercol=2 then
	yearly = ChangedCell.Value/14*365
elseif usercol=3 then
	yearly = ChangedCell.Value*12
elseif usercol=4 then
	yearly = ChangedCell.Value*4
elseif usercol=5 then
	yearly = ChangedCell.Value
End If


For changingcol = 0 to 4
	QuietCell = oRange.getCellByPosition(changingcol,userrow)
	QuietCell.RemoveModifyListener(oListener)

	If changingcol=0 then
		QuietCell.SetValue(yearly/365*7)
	elseif changingcol=1 then
		QuietCell.SetValue(yearly/365*14)
	elseif changingcol=2 then
		QuietCell.SetValue(yearly/12)
	elseif changingcol=3 then
		QuietCell.SetValue(yearly/4)
	elseif changingcol=4 then
		QuietCell.SetValue(yearly)
	End If

next changingcol


End Sub

Sub MyApp_disposing(oEvent)
' msgbox "disposing of the listener" 
End Sub

Does anyone have any help for this?

[FreakWent]

Well, I got it myself in the end.

There's no disposer, And the thing loops if the macro gets run twice, so every time I made a change I had to shut down the existing macros before trying the new change.

Ideally, it should have a checker and a msgbox handler that tells you "An instance of this macro is running already" or something like that.

This code works for me. I hope it helps someone else!

Code: Select all

 Global oRange As Object
 Global oListener    As Object
 Global loopcop
 Global CellArray (52,5) As Object

Sub SetupIntegrity
	loopcop=0
    oScalcDocument=ThisComponent
    oSheet = oScalcDocument.Sheets(0)
    
    oRange = oSheet.getCellRangebyName("B9:F60")
    oListener = createUnoListener("MyApp_", "com.sun.star.util.XModifyListener")

	dim oCell as object
 
 	For row = 0 to 51
		 For col = 0 to 4
			TempCell = oRange.getCellByPosition(col,row)
			TempCell.addModifyListener(oListener)
			CellArray(row,col) = TempCell
		Next col
	Next row

End Sub


 

Sub MyApp_Modified (oEvent)

	For row = 0 to 51
		 For col = 0 to 4

			CellArray(row,col).RemoveModifyListener(oListener)

		Next col
	Next row

	ChangedCell = oEvent.Source

	userrow = ChangedCell.CellAddress.Row-8
	usercol = ChangedCell.CellAddress.Column

	If usercol=1 then
		yearly = ChangedCell.Value/7*365
	elseif usercol=2 then
		yearly = ChangedCell.Value/14*365
	elseif usercol=3 then
		yearly = ChangedCell.Value*12
	elseif usercol=4 then
		yearly = ChangedCell.Value*4
	elseif usercol=5 then
		yearly = ChangedCell.Value
	End If


	For changingcol = 0 to 4
		If changingcol=0 then
			CellArray(userrow,changingcol).SetValue(yearly/365*7)
		elseif changingcol=1 then
			CellArray(userrow,changingcol).SetValue(yearly/365*14)
		elseif changingcol=2 then
			CellArray(userrow,changingcol).SetValue(yearly/12)
		elseif changingcol=3 then
			CellArray(userrow,changingcol).SetValue(yearly/4)
		elseif changingcol=4 then
			CellArray(userrow,changingcol).SetValue(yearly)
		End If
	Next changingcol

	For row = 0 to 51
		 For col = 0 to 4

			CellArray(row,col).AddModifyListener(oListener)

		Next col
	Next row

End Sub

[iamoraal]

I was just looking for something like this. I wanted a "Validity" list to open when user tabs or clicks on a cell. Only have a single column to add the listener to. Can you point me in the right direction to find the correct event / command to drop the listdown?
Thanks for posting your solved code!

[twilli3141]

Freakwent:

Since I don't speak VB, I'm having trouble seeing what you did that solved your problem with turning off the listeners.

I am having trouple with

XModifyBroadcaster.removeModifyListener(XModifyListener)

not actually removing the listener.

Can you explain generically how you "turned off" the listeners?

Thanks..

[Villeroy]

Right-click sheet tab > Sheet Events...
Event: Content changed

Code: Select all

Sub OnSheetContentChange(e)
print e.AbsoluteName
End Sub

The passed event object is the modified cell, range or multiple ranges.