OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

EVENT LISTENERS troubles & stop working, CALC-OOBasic, H

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
michelr.be
Newbie
Newbie


Joined: 17 Feb 2008
Posts: 3

PostPosted: Sun Sep 14, 2008 8:59 am    Post subject: EVENT LISTENERS troubles & stop working, CALC-OOBasic, H Reply with quote

I have a lot of troubles with the event listeners in OOBasic calc.

I would love to have a sub that always will be triggers when anything changes in the spreadsheet. Providing me then also with:
range-objects: oActiveCell, oPreviousActiveCell, oActiveSelection, oPreviousSelection
strings: vPreviousValueFromActiveCell, vPreviousValueFromPreviousActiveCell

If anyone can provide more information or has some solutions to this, please help me.

So far the problems with the some of the listeners:

XModifyListener: Doesn't tell me what has changed with the (oEvent.source). I also can't find what has been changed because the range from a auto-drag-and-fill or copy-paste multiple cells isn't been selected until the "Sub XModifyListener_modified" ends. Also I can't know what the previous cell-stringvalue was.

XChartDataChangeEventListener: This give the selection that has been changed, but sometimes it's stop working. Mostly when deleting or inserting rows ! But I also don't know what the previous cell-stringvalue was.

XSelectionChangeListener: The main problem is that when you pop-up a question your mouseclick-release isn't been recorded jet! So the mouse-movement still changes the selection while you want to click on the msgbox and even after that pop-up-message has gone. So the selection is changing without the mousebutton is pressed!!!!!

XSelectionChangeListener in combination with XModifyListener was promissing with the problem above, but now it seems that one can't control which of the two listeners runs first. Atleast that is what I think is the reason for not always reacting the same. Also these listeners sometimes suddenly stop working. Code working with trouble:

global oListenerRange as object, oListenerModify as object, oListenerChart as object, oListenerSelection as object
global bListenerPaused as boolean
global oCurSel as object 'current selection
global oCurCell as object 'current active cell
global vCurVal as string 'current value from current cell
global oPreSel as object 'previous selection range
global oPreCell as object 'previous active cell
global vPreVal as string 'previous value of previous active cell
global vNewVal as string 'new value of previous active cell
global vPreLastRow as integer 'previous last row of complete active sheet
global vPreLastCol as integer 'previous last column of complete active sheet
global bModified as boolean

sub sActivating 'triggered by CALC/Tools/Customize/Events/"Activate Document"
sListenerSelection_selectionChanged(0) 'Initialize variables after programming
end sub
sub sDocumentOpening 'triggered by CALC/Tools/Customize/Events/"Open Document"
sStartListener 'only one time to initialize event-listeners
end sub
sub sDocumentClosing 'triggered by CALC/Tools/Customize/Events/"Close Document"
sStopListener
end sub

' ---------------------------------- LISTENERS -----------------------------------------
Sub sStartListener 'start event intercepting
bListenerPaused=false
oListenerRange=thisComponent.sheets.getByIndex(0)

oListenerModify=createUnoListener("sListenerModify_", "com.sun.star.util.XModifyListener")
oListenerRange.AddModifyListener(oListenerModify) 'Triggers before changing selection

oListenerSelection=CreateUnoListener("sListenerSelection_", "com.sun.star.view.XSelectionChangeListener")
thisComponent.currentController.addSelectionChangeListener(oListenerSelection)

' oListenerChart=createUnoListener("sListenerChart_", "com.sun.star.chart.XChartDataChangeEventListener")
' oListenerRange.addChartDataChangeEventListener(oListenerChart) 'Triggers after changing selection but sometimes just stops working !!!
End Sub
Sub sStopListener 'only works when there is no programming done. Seems listener-objects get lost !!!
' If not IsNull(oListenerModify) then oListenerRange.removemodifyListener(oListenerModify) '=XModifyListener
' If not IsNull(oListenerSelection) then thisComponent.currentController.removeEventListener(oListenerSelection) '=XSelectionChangeListener
' If not IsNull(oListenerChart) then oListenerRange.removeChartDataChangeEventListener(oListenerChart) '=XChartDataChangeEventListener
End Sub

Sub sListenerModify_modified(oEvent) '=XModifyListener: Triggers before changing selection !!!
if bListenerPaused then exit sub
if thisComponent.CurrentSelection(0).getCellByposition(0,0).string="" and oCurSel(0).columns.count<255 then
if msgbox ("Wipe this data?!",305,"!!! 1 or more cells deleted !!!") = 2 then sUndo '=cancel
' msgbox "Previous selection: " & left(fAddress(oPreSel)&" ",9) & " Previous cell: " & fAddress(oPreCell) & " = " & chr(34) & vPreVal & chr(34) & " >>> " & chr(34) & vNewVal & chr(34) & chr(10) & " Current selection: " & left(fAddress(oCurSel)&" ",9) & " Current cell: " & fAddress(oCurCell) & " = " & chr(34) & vCurVal & chr(34)
end if
bModified=true
End Sub
Sub sListenerSelection_selectionChanged(oEvent) '=XSelectionChangeListener: deleting cell-contense doesn't trigger this !!!
if isNull(oCurSel) then 'Run only when initializating (first run or after programming)
oCurSel=thisComponent.CurrentSelection : oCurCell=fActiveCell() : vCurVal=oCurCell.string
vPreLastRow=fLastRow : vPreLastCol=fLastColumn
end if
oPreSel=oCurSel : oPreCell=oCurCell: vPreVal=vCurVal
oCurSel=thisComponent.CurrentSelection : oCurCell=fActiveCell() : vCurVal=oCurCell.string
vNewVal=oPreCell.string
bPreSelIsOneCell=oPreSel.supportsService("com.sun.star.sheet.SheetCell")
bCurSelIsOneCell=oCurSel.supportsService("com.sun.star.sheet.SheetCell")
if bListenerPaused then exit sub
if vNewVal<>"" and vNewVal<>vPreVal and bPreSelIsOneCell and bCurSelIsOneCell then '1 cell changed
if vPreVal<>"" then if msgbox ("Value " & chr(34) & vPreVal & chr(34) & " will be overwritten with" & chr(34) & vNewVal & chr(34) & " ?",33,"Data overschriving") = 2 then sUndo '=cancel
' Finally 1 entry hase been changed and one can run other checks to wat has been entered
elseif fLastRow < vPreLastRow then 'row deleted !
if UCase(InputBox(chr(10) & chr(10) & chr(10) & "To remove complete lines type 'YES':","!!! DELETED LINE(S) !!!"))<>"YES" then sUndo '=cancel
elseif fLastColumn < vPreLastCol then 'column deleted !
if UCase(InputBox(chr(10) & chr(10) & chr(10) & "To remove complete columns type 'YES':","!!! DELETED COLUMN(S) !!!"))<>"YES" then sUndo '=cancel
elseif bModified and not bCurSelIsOneCell and fAddress(oCurCell)=fAddress(oPreCell) and oCurSel(0).columns.count<255 then 'more then 1 cell changed but not hole line
if msgbox ("Do you want to alter more then 1 cell?!",305,"!!! More then 1 cell has changed !!!") = 2 then sUndo '=cancel
end if
bModified=false
vPreLastRow=fLastRow : vPreLastCol=fLastColumn
oCurSel=thisComponent.CurrentSelection : oCurCell=fActiveCell() : vCurVal=oCurCell.string
End Sub
Sub sListenerChart_chartDataChanged(oEvent) '=XChartDataChangeEventListener: 'Triggers after changing selection
End Sub

Sub sListenerModify_disposing(oEvent) 'needed for XModifyListener
Msgbox "When is this sListenerModify_disposing ever going to be triggered?"
End Sub
Sub sListenerSelection_disposing(oEvent) 'needed for SelectionChangeListener
Msgbox "When is this sListenerSelection_disposing ever going to be triggered?"
End Sub
Sub sListenerChart_disposing(oEvent) 'needed for XChartDataChangeEventListener
Msgbox "When is this sListenerChart_disposing ever going to be triggered?"
End Sub

' ---------------------------------- UTILITIES -----------------------------------------
Sub sScreenUpdatingOff
thisComponent.currentController.Frame.ContainerWindow.Enable=False
if thisComponent.isActionLocked=false then thisComponent.addActionLock
if thisComponent.hasControllersLocked=false then thisComponent.LockControllers
bListenerPaused=true
End Sub
Sub sScreenUpdatingOn
if thisComponent.hasControllersLocked=true then thisComponent.unLockControllers
if thisComponent.isActionLocked=true then thisComponent.removeActionLock
thisComponent.currentController.Frame.ContainerWindow.Enable=True
bListenerPaused=false
End Sub

sub sUndo()
bListenerPaused=true
oCell=fActiveCell() 'get activecell
createUnoService("com.sun.star.frame.DispatchHelper").executeDispatch(thisComponent.currentController.Frame, ".uno:Undo", "", 0, Array()) '= undo
if not isError(oCell) then thisComponent.currentController.Select(oCell) 'reselect previous activecell
thisComponent.currentController.Select(thisComponent.CreateInstance("com.sun.star.sheet.SheetCellRanges")) 'unselect ranges
bListenerPaused=false
end sub
sub sRedo()
bListenerPaused=true
oCell=fActiveCell() 'get activecell
createUnoService("com.sun.star.frame.DispatchHelper").executeDispatch(thisComponent.currentController.Frame, ".uno:Redo", "", 0, Array()) '= repeat
if not isError(oCell) then thisComponent.currentController.Select(oCell) 'reselect previous activecell
thisComponent.currentController.Select(thisComponent.CreateInstance("com.sun.star.sheet.SheetCellRanges")) 'unselect ranges
bListenerPaused=false
end sub

function fAddress(oIn) 'Give address with column-letters and without sheetname or $
if isNull(oIn) then fAddress="" : exit function
vOut=oIn.absolutename
vOut=join(split(vOut,"$"),"") 'replace $ with ""
vSheetName=left(vOut,instr(2,vOut,"."))
fAddress=join(split(vOut,vSheetName),"") 'replace sheetname with ""
end function

Function fLastRow() as integer
oSheet = ThisComponent.CurrentController.ActiveSheet
oCursor = oSheet.createCursor()
oCursor.GotoEndOfUsedArea(False)
fLastRow = oCursor.RangeAddress().EndRow
End Function
Function fLastColumn() as integer
oSheet = ThisComponent.CurrentController.ActiveSheet
oCursor = oSheet.createCursor()
oCursor.GotoEndOfUsedArea(False)
fLastColumn = oCursor.RangeAddress().EndColumn
End Function
Function fLastCell() as object
oSheet = ThisComponent.CurrentController.ActiveSheet
oCursor = oSheet.createCursor()
oCursor.GotoEndOfUsedArea(False)
vLastColumn = oCursor.RangeAddress().EndColumn
vLastRow = oCursor.RangeAddress().EndRow
fLastCell = oSheet.getCellByPosition(vLastColumn,vLastRow)
End Function
function fUsedRange()
oSheet = ThisComponent.CurrentController.ActiveSheet
oCursor = oSheet.createCursor()
oCursor.gotoStartOfUsedArea(False)
nFirstRow = oCursor.getRangeAddress().StartRow
nFirstCol = oCursor.getRangeAddress().StartColumn
oCursor.gotoEndOfUsedArea(False)
nLastRow = oCursor.getRangeAddress().EndRow
nLastCol = oCursor.getRangeAddress().EndColumn
fUsedRange = oSheet.getCellRangeByPosition(nFirstCol,nFirstRow,nLastCol,nLastRow)
end function

Function fActiveCell(optional vChangeColOrRow) as object 'get the activecell with or whitout changing to an other row or column, column=columnname, row=number-1
oActiveSheet=thisComponent.currentController.activeSheet
vViewData=thisComponent.currentController.viewData
vViewData=join(split(vViewData,";"),"/") 'replace ; with /
vViewData=join(split(vViewData,":"),"/") 'replace : with /
vViewData=join(split(vViewData,"+"),"/") 'replace + with /
vViewData=split(vViewData,"/") 'split the string
iCol=val(vViewData(6))
iRow=val(vViewData(7))
if not isError(vChangeColOrRow) then 'change row or column
if isNumeric(vChangeColOrRow) then 'change row
iRow=int(val(vChangeColOrRow))
else 'change column
for iCol=0 to 999
if oActiveSheet.getCellByPosition(iCol,0).string=vChangeColOrRow then exit for
if oActiveSheet.getCellByPosition(iCol,0).string="" then 'Column-label not found
msgbox "Column-name " & chr(34) & vChangeColOrRow & chr(34) & " is not found !!!"
exit function
endIf
next
endIf
endIf
fActiveCell=oActiveSheet.getCellByPosition(iCol,iRow)
End function
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group