Cell Change Event

[radhika_24]

Hello guys,

Its been really really long that I am trying to find a solution to this .. !!
Actually, i have a macro , to which i need to assign an event that would run whenever the user makes any changes in the cell selected..
I have tried to use Xdatachangelistener and Xselectionlistener ..
But the event just doesnt fire up..
I am sure about this .. because .. When i manually run the code.. or with a toolbar icon... the code runs perfectly .. There seems to be a major problem with the event assignment though !! ..
I am pasting my code here.. Hope to get early help

Sub Main
Dim oDocument As Object, oSheet As Object, oCell As Object, bCell As Object
Dim oSelection as Object
Dim oRange as Object'
oDocument = ThisComponent
'Dim r,c as integer
Dim oRangeAddress as New com.sun.star.table.CellRangeAddress

oDocument = StarDesktop.ActiveFrame.Controller.Model
oSheet = oDocument.CurrentController.getActiveSheet() ' get actual sheet

'oSelection = oDocument.CurrentController.getSelection
'oRangeAddress = oSelection.getRangeAddress ' get actual range / obtiene el rango actual

'r = oRangeAddress.StartRow 'current row / la fila actual
'c = oRangeAddress.StartColumn 'current column / la columna actual
oCell = oSheet.getCellByPosition(0,0)
oListener = CreateUnoListener( "OOO_", "com.sun.star.chart.XChartDataChangeEventListener" )
oCell.addChartDataChangeEventListener(oListener)
End Sub

Sub OOO_chartDataChanged
Dim oDocument As Object, oSheet As Object, oCell As Object, bCell As Object
Dim oSelection as Object
Dim oRange as Object
oDocument = ThisComponent
'Dim r,c as integer
Dim oRangeAddress as New com.sun.star.table.CellRangeAddress

oDocument = StarDesktop.ActiveFrame.Controller.Model
oSheet = oDocument.CurrentController.getActiveSheet() ' get actual sheet

'oSelection = oDocument.CurrentController.getSelection
'oRangeAddress = oSelection.gItem NoetRangeAddress ' get actual range / obtiene el rango actual

'r = oRangeAddress.StartRow 'current row / la fila actual
'c = oRangeAddress.StartColumn 'current column / la columna actual
oCell = oSheet.getCellByPosition(0,0)
'oSelection = oDocument.CurrentController.getSelection
'oRangeAddress = oSelection.getRangeAddress ' get actual range / obtiene el rango actual
bCell = oSheet.getCellByPosition(0,2)
intanswer = NumToWords(oCell.getValue)
if intanswer = "Zero" Then
if bCell.getValue > 0 Then
bCell.setString(bCell.getValue)
else
bCell.setString("")
EndIf
Else
bCell.setString("Rs. "+intanswer)
EndIf
End Sub


And then the numtowords function follows later ..

Thanks in advance,
Radhika

[probe1]

I haven't debugged your code (which part runs if called manually?)

But I'm pretty sure you don't need a macro here.

If you want to translate a number given in A1 to words in A3, do a formula in A3:
=If( A1<>""; NumToWords( A1 ); "")

using this custom function (stored in Standard library).


Does this solve your initial problem?

(I had no problem adopting the code posted by ms777 in this thread
_________________
Cheers
Winfried
My Macros
DateTime2 extension: insert date, time or timestamp, formatted to your needs

[radhika_24]

The part that runs manually.. is the "main" . because thats where i have assigned the listeners. After your post, I also checked with the other function... OOO_chartDataChanged - this function also works manually.

Even the built in events of macro - opening document events - dont seem to work properly. Is there some other problem?? Or may be my understanding of macros is not correct. One of the two for sure.

I also saw the link that you had posted by my777 .. I dont think I have done anything different..And my code just doesnt need to run for A1 . It has to run for the cell that the user has currently selected.. I have changed my code a little bit. I am pasting it again.. Thanks for bothering .. !!

Sub Main
Dim oDocument As Object, oSheet As Object, oCell As Object, bCell As Object
Dim oSelection as Object
Dim oRange as Object'
oDocument = ThisComponent
Dim r,c as integer
Dim oRangeAddress as New com.sun.star.table.CellRangeAddress

oDocument = StarDesktop.ActiveFrame.Controller.Model
oSheet = oDocument.CurrentController.getActiveSheet() ' get actual sheet

oSelection = oDocument.CurrentController.getSelection
oRangeAddress = oSelection.getRangeAddress ' get actual range / obtiene el rango actual

r = oRangeAddress.StartRow 'current row / la fila actual
c = oRangeAddress.StartColumn 'current column / la columna actual
oCell = oSheet.getCellByPosition(c,r)
oListener = CreateUnoListener( "OOO_", "com.sun.star.chart.XChartDataChangeEventListener" )
oCell.addChartDataChangeEventListener(oListener)
OOO_chartDataChanged oCell
End Sub

Sub OOO_chartDataChanged
Dim oDocument As Object, oSheet As Object, oCell As Object, bCell As Object
Dim oSelection as Object
Dim oRange as Object
oDocument = ThisComponent
Dim r,c as integer
Dim oRangeAddress as New com.sun.star.table.CellRangeAddress

oDocument = StarDesktop.ActiveFrame.Controller.Model
oSheet = oDocument.CurrentController.getActiveSheet() ' get actual sheet

oSelection = oDocument.CurrentController.getSelection
oRangeAddress = oSelection.getRangeAddress ' get actual range / obtiene el rango actual

r = oRangeAddress.StartRow 'current row / la fila actual
c = oRangeAddress.StartColumn 'current column / la columna actual
oCell = oSheet.getCellByPosition(c,r)
if c > 2 Then
bCell = oSheet.getCellByPosition(c-2,r+2)
else
bCell = oSheet.getCellByPosition(c,r+2)
EndIf
intanswer = NumToWords(oCell.getValue)
if intanswer = "Zero" Then
if bCell.getValue > 0 Then
bCell.setString(bCell.getValue)
else
bCell.setString("")
EndIf
Else
bCell.setString("Rs. "+intanswer)
EndIf
End Sub


thanks again!!
Radhika

[probe1]

Looking at your code again (put it in the CODE tags for betting reading [select all statements, hit the button on top of the edit window in this forum software], please), I think I got the problem:

you're (double!) declaring variables inside the SUBs: put them on top of the code, as GLOBAL, so their lifetime is from invocation until OOo ends.

Does it do the trick?
_________________
Cheers
Winfried
My Macros
DateTime2 extension: insert date, time or timestamp, formatted to your needs

[radhika_24]

[probe1]

You don't pass the event to OOO_chartDataChanged.
You don't call a RemoveListener code: Removing the Listeners is essential for not interferring OOo's stability.

Have a look at my (working) example onColumnChange

[radhika_24]

Can you tell me.. What is this part doing ??? (Red part)


Sub O1_selectionChanged(aEvent as com.sun.star.lang.EventObject)
'xray.xray aEvent
oSel = aEvent.Source.Selection
if HasUnoInterfaces(oSel, "com.sun.star.sheet.XCellAddressable") then
oCA = oSel.CellAddress
actCol = lastCol
actRow = lastRow
lastCol = oCA.Column
lastRow = oCA.Row
endif
end sub
(Sorry could not put this into code format .. else the red color wouldnt come ! )

Cause in my case .. i already have a code which acessess the cells that the user has selected .. so . then what parameter should i pass to the event listener function ... cause otherwise it tells me. .. wrong number of parameters!!

Thanks a trilllion for the consideration!!!
Radhika

[probe1]

if you use event listeners, this part determines from which address the function is called.
This was your aim: if something changes in a cell you are watching, you want to know...

No need to use other code, because, if you entered a value, lets say, in A15 then ENTER and click on C20, the selection is there!
With the event object you know that A15 was changed...
_________________
Cheers
Winfried
My Macros
DateTime2 extension: insert date, time or timestamp, formatted to your needs

[radhika_24]

Thanks thanks a trillion for all the precious time you spent for me ..
But while coding, my client told me that .. he needs it only for one particular cell and not many at a time ...
So i believe that a toolbar button would do better for the same
So.. basically I switched back to my old code

But, for me , for my knowledge and practice.. I am still going to continue working on that code and see where my mistake lies. . (and trouble you more )

Thanks a zillion!!!

Enjoy ur vacation

Radhika