[Solved] How to know which cell changed?

[carlosrevespt]

Hi there!

I have implemented a listener with the following code:

Code: Select all

Global oListener as Object
Global CellRng as Object

Sub AddListener
   Dim Doc, Sheet, Cell as Object

   Doc = ThisComponent
   Sheet = Doc.Sheets.getByIndex(0) 

   CellRng = Sheet.getCellrangeByName("B1:B10")

   oListener = createUnoListener("Modify_","com.sun.star.util.XModifyListener")
   Cellrng.addModifyListener(oListener) 

End Sub

Sub Modify_modified(oEv)
   CalledRoutine
End Sub

Sub Modify_disposing(oEv)
End Sub

Sub RmvListener
   CellRng.removeModifyListener(oListener)
End Sub

Sub CalledRoutine
	Doc = ThisComponent
	Sheet = Doc.Sheets.getByIndex(0)
	Cell = Sheet.getCellByPosition(3,1)
	CurrentVal = Cell.Value
	Cell.Value = CurrentVal + 1
End Sub

Now my question is: Can i know which cell within the Range was changed and trigered the event?

If i use

Code: Select all

   oEv.Source.AbsoluteName

it gives me the Range and not the individual cell...

Thanks

[Villeroy]

Using a predefined script event: viewtopic.php?f=9&t=80490

Why do you ignore the passed event struct in your code?

[carlosrevespt]

Hi Villeroy

Thanks for your answer.
i'm sorry if my understanding of OOo Basic is not the best...
See i'm not a programmer. I'm just constructing a speadsheet and needed to make a dialog to fill up some criteria and needed to start to learn how to program macros.
So i'm starting and all Basic structure is a bit confusing for me still.

But let me explain a bit more what i'm doing.
As i told in the spreadsheet there will be up to 5 criteria that the users can input. I'm programming the input fase of the criteria so that most of the calculations and formulas are automatically made.
The criteria can be automatic or manual and i only want to monitor the ceels where they are manual.
Since each of the criteria (columns) have about 100 rows i can end up with up to 500 cells to monitor for changes.
Thats why i was trying to implement listeners for the criteria. That would end up with 5 listeners max. But after inspecting the methods and properties of the event from the listener i didn't find a way to know which individual cell was changed.
i could eventually implement listeners for each individual cell but i don't like the idea of 500 listeners...

Thats the reason of my question. i don't know if the solution you sent in the link can solve my problem...

As i said earlier, sorry for my limited understanding of the programming.

Edit: Btw... As far as i researched the com.sun.star.util.XModifyListener only has 2 methods: modified() and disposing()... Both are there in my code... So what do u mean with ignoring the passed event struct?

[carlosrevespt]

Ok... found another way.

I just need to assign a macro to the Content Changed Event Handler of the sheet and then it's easy to get the address of the cell that triggered the event:

Code: Select all

Sub ChangedCell (oEvent as Object)

	CellAdd = oEvent.getCellAddress()
	MsgBox CellAdd.Row
	MsgBox CellAdd.Column
	MsgBox oEvent.Value

End Sub

Now the only problem is how to assign the macro to the event handler by code...
I think it will be something through

Code: Select all

ThisComponent.Sheets(0).Events

but still didn't found out how exactly...

[carlosrevespt]

Got it!!!

Code: Select all

Sub AddEventHandler

 	Dim myEvent(1) as new com.sun.star.beans.PropertyValue
 	
	myEvent(0).Name = "EventType"
 	myEvent(0).Value = "Script"
 	myEvent(1).Name = "Script"
 	myEvent(1).Value = "vnd.sun.star.script:Standard.Module2.MyChange?language=Basic&location=document"

	ThisComponent.Sheets(0).Events.ReplaceByName("OnChange", myEvent())

End Sub

Sub RemoveEventHandler

	Dim myEvent(1) as new com.sun.star.beans.PropertyValue
  
	myEvent(0).Name = ""
	myEvent(0).Value = ""
	myEvent(1).Name = ""
	myEvent(1).Value = ""

	ThisComponent.Sheets(0).Events.ReplaceByName("OnChange", myEvent())

End Sub

Now i think its good to go!

[Villeroy]

Script events are saved with the document/template. You don't need to create them programmatically.

[carlosrevespt]

I know Villeroy!
But since i just want to monitor changes when manual criteria are choosen then i need to enable/disable the events accordingly!

Thanks