[Solved] [Calc] Listeners for cell modification events

Creating a macro - Writing a Script - Using the API

[Solved] [Calc] Listeners for cell modification events

Postby FreakWent » Wed May 12, 2010 11:06 am

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!
Last edited by FreakWent on Fri May 14, 2010 11:56 pm, edited 1 time in total.
ooo 3.1.1 on Gentoo!
FreakWent
 
Posts: 3
Joined: Wed May 12, 2010 9:52 am

Re: [CALC, Basic] Listeners for cell modification events

Postby FreakWent » Thu May 13, 2010 1:08 pm

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   Expand viewCollapse view
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?
ooo 3.1.1 on Gentoo!
FreakWent
 
Posts: 3
Joined: Wed May 12, 2010 9:52 am

Re: [CALC, Basic][Solved] Listeners for cell modification ev

Postby FreakWent » Fri May 14, 2010 11:55 pm

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   Expand viewCollapse view
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
ooo 3.1.1 on Gentoo!
FreakWent
 
Posts: 3
Joined: Wed May 12, 2010 9:52 am

Re: [Solved] [Calc] Listeners for cell modification events

Postby iamoraal » Sat May 15, 2010 12:33 am

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!
OpenOffice 3.2.0 on Mac OS 10.5.8
iamoraal
 
Posts: 5
Joined: Sat May 15, 2010 12:10 am

Re: [Solved] [Calc] Listeners for cell modification events

Postby twilli3141 » Fri Jun 04, 2010 6:41 am

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..
OpenOffice 3.1.1 on Windows XP
twilli3141
 
Posts: 20
Joined: Tue Feb 23, 2010 12:22 am

Re: [Solved] [Calc] Listeners for cell modification events

Postby Villeroy » Mon Nov 28, 2016 3:08 pm

Right-click sheet tab > Sheet Events...
Event: Content changed
Code: Select all   Expand viewCollapse view
Sub OnSheetContentChange(e)
print e.AbsoluteName
End Sub

The passed event object is the modified cell, range or multiple ranges.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27869
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 4 guests