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

Creating a macro - Writing a Script - Using the API

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

Postby ooGuillaume » Mon Oct 21, 2019 11:54 am

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 16.04
ooGuillaume
 
Posts: 13
Joined: Sat Jul 20, 2019 10:03 am

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

Postby JeJe » Mon Oct 21, 2019 12:14 pm

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

http://www.pitonyak.org/oo.php
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 631
Joined: Wed Mar 09, 2016 2:40 pm

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

Postby Zizi64 » Mon Oct 21, 2019 12:22 pm

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; LO6.1.6 on Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.3.3; AOO4.1.6
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.
User avatar
Zizi64
Volunteer
 
Posts: 8557
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Postby ooGuillaume » Mon Oct 21, 2019 1:47 pm

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 16.04
ooGuillaume
 
Posts: 13
Joined: Sat Jul 20, 2019 10:03 am

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

Postby JeJe » Mon Oct 21, 2019 2:13 pm

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   Expand viewCollapse view

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
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 631
Joined: Wed Mar 09, 2016 2:40 pm

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

Postby JeJe » Mon Oct 21, 2019 2:20 pm

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

Code: Select all   Expand viewCollapse view

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)
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 631
Joined: Wed Mar 09, 2016 2:40 pm

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

Postby Lupp » Mon Oct 21, 2019 2:26 pm

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 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2571
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

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

Postby JeJe » Mon Oct 21, 2019 2:33 pm

Slight variation on the above using keyfunc:

Code: Select all   Expand viewCollapse view

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

Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 631
Joined: Wed Mar 09, 2016 2:40 pm

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

Postby Villeroy » Mon Oct 21, 2019 3:14 pm

One of these horrible UNO listeners:
Code: Select all   Expand viewCollapse view
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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27395
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby ooGuillaume » Mon Oct 21, 2019 4:51 pm

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 16.04
ooGuillaume
 
Posts: 13
Joined: Sat Jul 20, 2019 10:03 am

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

Postby Villeroy » Mon Oct 21, 2019 4:59 pm

Easy!
Code: Select all   Expand viewCollapse view
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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27395
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby Lupp » Mon Oct 21, 2019 5:17 pm

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 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2571
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

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

Postby JeJe » Mon Oct 21, 2019 5:31 pm

You may be able to get some information about the type of change by calling thiscomponent.undomanager.getCurrentUndoActionTitle
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 631
Joined: Wed Mar 09, 2016 2:40 pm

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

Postby ooGuillaume » Tue Oct 22, 2019 12:31 pm

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 16.04
ooGuillaume
 
Posts: 13
Joined: Sat Jul 20, 2019 10:03 am

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

Postby Lupp » Tue Oct 22, 2019 3:53 pm

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 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2571
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 5 guests