[Calc] "Content Changed" sheet event behavior with "Ctrl+X"

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
ooGuillaume
Posts: 16
Joined: Sat Jul 20, 2019 10:03 am

[Calc] "Content Changed" sheet event behavior with "Ctrl+X"

Post by ooGuillaume »

Hello,

I'm using LibreOffice 6.2.7.1 Calc on Xubuntu 16.04.

I set up a macro to run when the "Content Changed" sheet event is thrown.
The macro triggers fine except when I cut (Control + X) the current selection.

Is this to be expected?
I'd like the macro to run when I cut the current selection. Is there a workaround, or another solution?

Thank you.
LibreOffice 6.2.7.1 on Xubuntu 18.04
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: [Calc] "Content Changed" sheet event behavior with "Ctrl

Post by JeJe »

See Andrew Pitonyak's OpenOffice.org Macros Explained OOME Third Edition
Listing 282 intercepting dispatch commands

http://www.pitonyak.org/oo.php
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: [Calc] "Content Changed" sheet event behavior with "Ctrl

Post by Zizi64 »

Yopu can use the "'Modified' status was changed" event of the document.

Note you can not use it again while the state of the flag is true. You must delete the flag at the end of the process launched by the event.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
ooGuillaume
Posts: 16
Joined: Sat Jul 20, 2019 10:03 am

Re: [Calc] "Content Changed" sheet event behavior with "Ctrl

Post by ooGuillaume »

The warning in this section of Andrew Pitonyak's book is scary to me.
Be very careful while writing listeners and intercepting dispatches. Assume that something will change, or that you did something wrong and that OOo will crash; you have been warned.
I naively copied and pasted the whole Listing 282 into one module and hoped I would see a Message Box when copying a cell to another in my spreadsheet.
Nothing happened. Do I need to "hook" one or several functions inside this code, to a an event somewhere? I'm blind here. :)

Zizi64, thanks for the suggestion.
How would I know, when this event is triggered, that some contents were modified in the current selection?
LibreOffice 6.2.7.1 on Xubuntu 18.04
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: [Calc] "Content Changed" sheet event behavior with "Ctrl

Post by JeJe »

Pitonyak's code modified for cut is this (you have to run registerInterceptor first) but unfortunately on quickly testing it doesn't fire if a cut takes place in one cell.

Code: Select all


Global oDispatchInterceptor
Global oSlaveDispatchProvider
Global oMasterDispatchProvider
Global oFrame
Global bDebug As Boolean
Dim oCopyDispatch
Sub RegisterInterceptor
oFrame = ThisComponent.currentController.Frame
oDispatchInterceptor = CreateUnoListener("ThisFrame_", _
"com.sun.star.frame.XDispatchProviderInterceptor")
oFrame.registerDispatchProviderInterceptor(oDispatchInterceptor)
End Sub
Sub ReleaseInterceptor()
On Error Resume Next
oFrame.releaseDispatchProviderInterceptor(oDispatchInterceptor)
End Sub
Function ThisFrame_queryDispatch ( oUrl, _
sTargetFrameName As String, lSearchFlags As Long ) As Variant
302
Dim oDisp
Dim sUrl As String
'slot protocol causes OOo crash...
if oUrl.Protocol = "slot:" Then
Exit Function
End If
If bDebug Then
Print oUrl.complete
End If
'do your dispatch management here:
Select Case oUrl.complete
'Case ".uno:Copy"
'oDisp = GetCopyDispatch 'replace the original dispatch
'Case ".uno:Paste"
'oDisp = GetCopyDispatch 'replace the original dispatch
'Case ".uno:Save"
'oDisp = GetCopyDispatch 'replace the original dispatch
'Case ".uno:Undo"
'oDisp = GetCopyDispatch 'replace the original dispatch
'Case ".uno:blabla"
'do something
Case ".uno:Cut"
oDisp = GetCopyDispatch 'replace the original dispatch
Case Else
oDisp = _
oSlaveDispatchProvider.queryDispatch( oUrl, sTargetFrameName, lSearchFlags )
End Select
ThisFrame_queryDispatch = oDisp
End Function
Function ThisFrame_queryDispatches ( mDispatches ) As Variant
'ThisFrame_queryDispatches = mDispatches()
End Function
Function ThisFrame_getSlaveDispatchProvider ( ) As Variant
ThisFrame_getSlaveDispatchProvider = oSlaveDispatchProvider
End Function
Sub ThisFrame_setSlaveDispatchProvider ( oSDP )
oSlaveDispatchProvider = oSDP
End Sub
Function ThisFrame_getMasterDispatchProvider ( ) As Variant
ThisFrame_getMasterDispatchProvider = oMasterDispatchProvider
End Function
Sub ThisFrame_setMasterDispatchProvider ( oMDP )
303
oMasterDispatchProvider = oMDP
End Sub
Sub toggleDebug
bDebug = Not bDebug
End Sub
Function GetCopyDispatch()
If Not IsNull(oCopyDispatch) Then
oCopyDispatch = _
CreateUnoListener("MyCustom_", "com.sun.star.frame.XDispatch")
End If
GetCopyDispatch = oCopyDispatch
End Function
Sub MyCustom_dispatch(URL, Arguments)
Select Case URL.complete

Case ".uno:Cut"
MsgBox "cut"

'Case ".uno:Copy"
'MsgBox "Sorry, the original " & URL.complete & _
'" dispatch was stolen from Paolo M.", 48
'Case ".uno:Paste"
'ThisComponent.CurrentSelection(0).String = _
'"**** ARBITRARY CLIPBOARD CONTENT PROVIDED BY PAOLO M. ****"
'Case ".uno:Save"
'MsgBox "Sorry, the original " & URL.complete & _
'" dispatch was stolen from Paolo M.", 48
'Case ".uno:Undo"
'MsgBox "Undo What????!!!???", 16
'Case Else
End Select
End Sub
Sub MyCustom_addStatusListener(Control, URL)
End sub

Another option might be a keyhandler for the key combination of Ctrl+x

viewtopic.php?f=45&t=33914
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: [Calc] "Content Changed" sheet event behavior with "Ctrl

Post by JeJe »

Here's that code modified:
Run sStartXKeyHandler first then try pressing ctrl+x in your document.

Code: Select all


Option Explicit

global oXKeyHandler as object

sub sStartXKeyHandler
    if isNull(oXKeyHandler) then 'only if not jet running
        oXKeyHandler = CreateUnoListener("KeyHandler_", "com.sun.star.awt.XKeyHandler")
        ThisComponent.CurrentController.AddKeyHandler(oXKeyHandler)
    endif
end sub

sub sStopXKeyHandler
    if not IsNull(oXKeyHandler) then 'only if still running
        ThisComponent.CurrentController.removeKeyHandler(oXKeyHandler)
        oXKeyHandler = Nothing 'To know later this handler has stopped.
    end if
end sub

sub XKeyHandler_Disposing(oEvent)
end sub

function KeyHandler_KeyPressed(oEvent) as boolean
'    MsgBox(oEvent.KeyCode)
End function

function KeyHandler_KeyReleased(oEvent) as boolean
    KeyHandler_KeyReleased = False 'cancel KeyReleased


if oevent.modifiers = 2 and oevent.keycode =535 then
msgbox "cut"
end if

end function
Edit: (this won't of course work if a cut is performed using the mouse)
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Calc] "Content Changed" sheet event behavior with "Ctrl

Post by Lupp »

See my bug reports tdf#120465 (some months ago) and tdf#128296 (just now; including the older one).

https://bugs.documentfoundation.org/sho ... ?id=120465
https://bugs.documentfoundation.org/sho ... ?id=128296
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: [Calc] "Content Changed" sheet event behavior with "Ctrl

Post by JeJe »

Slight variation on the above using keyfunc:

Code: Select all


Option Explicit

global oXKeyHandler as object

sub sStartXKeyHandler
    if isNull(oXKeyHandler) then 'only if not jet running
        oXKeyHandler = CreateUnoListener("KeyHandler_", "com.sun.star.awt.XKeyHandler")
        ThisComponent.CurrentController.AddKeyHandler(oXKeyHandler)
    endif
end sub

sub sStopXKeyHandler
    if not IsNull(oXKeyHandler) then 'only if still running
        ThisComponent.CurrentController.removeKeyHandler(oXKeyHandler)
        oXKeyHandler = Nothing 'To know later this handler has stopped.
    end if
end sub

sub XKeyHandler_Disposing(oEvent)
end sub

function KeyHandler_KeyPressed(oEvent) as boolean
    KeyHandler_KeyPressed = False 'cancel KeyPressed
End function

function KeyHandler_KeyReleased(oEvent) as boolean
    KeyHandler_KeyReleased = False 'cancel KeyReleased

if oevent.keyfunc = 8 then 'cut function called
msgbox "cut"
end if

end function

Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Calc] "Content Changed" sheet event behavior with "Ctrl

Post by Villeroy »

One of these horrible UNO listeners:

Code: Select all

Sub addListener()
oModifyListener = createUnoListener("my_", "com.sun.star.util.XModifyListener")
ThisComponent.CurrentController.ActiveSheet.addModifyListener(oModifyListener)
End Sub

Sub my_modified(e)
	msgbox "Yep"
End Sub

Sub my_disposing(e)
REM required dummy routine called by
REM parent interface c.s.s.lang.XEventListener
End Sub
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
ooGuillaume
Posts: 16
Joined: Sat Jul 20, 2019 10:03 am

Re: [Calc] "Content Changed" sheet event behavior with "Ctrl

Post by ooGuillaume »

Thanks.

I'm having a hard time finding out how I can get the modified cell, cell range, or cell ranges, from the 'e' parameter of my_modified(e).
LibreOffice 6.2.7.1 on Xubuntu 18.04
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Calc] "Content Changed" sheet event behavior with "Ctrl

Post by Villeroy »

Easy!

Code: Select all

Sub my_modified(e)
  oMRI = CreateUnoService( "mytools.Mri" )
  oMRI.inspect(e)
End Sub
The Source element is always the object which got the listener attached.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Calc] "Content Changed" sheet event behavior with "Ctrl

Post by Lupp »

Villeroy wrote:The Source element is always the object which got the listener attached.
In the given example this is the Spreadsheet object, not a SheetCellRange or a SheetCellRanges object. To get access to the actually changed Cells the ThisComponent.CurrentSelection should help.
In case of a move by dragndrop that's the target range.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: [Calc] "Content Changed" sheet event behavior with "Ctrl

Post by JeJe »

You may be able to get some information about the type of change by calling thiscomponent.undomanager.getCurrentUndoActionTitle
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
ooGuillaume
Posts: 16
Joined: Sat Jul 20, 2019 10:03 am

Re: [Calc] "Content Changed" sheet event behavior with "Ctrl

Post by ooGuillaume »

Thank you all, I went for the ThisComponent.CurrentSelection combined with the ModifyListener.

Is it a problem that the listener is added on opening the file, but not removed afterwards?

Lupp, it indeed felt like a bug to me! If it ever gets fixed, I'll be able to simplify my code again.
LibreOffice 6.2.7.1 on Xubuntu 18.04
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Calc] "Content Changed" sheet event behavior with "Ctrl

Post by Lupp »

I would say it's surely a bug - and a very old one. It's common heritage of AOO and LibO.

Well, I reported the linked bugs, and I now also tried to draw the attention of a specific developer to these bugs. Lets practise patience again. There may not be a hurry concerning a bug unreported for decades.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Post Reply