Opening a Document in Listener Mode Help

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
User avatar
brookc84
Posts: 27
Joined: Sat Dec 07, 2013 3:52 pm

Opening a Document in Listener Mode Help

Post by brookc84 »

How do I write a listener routine/macro? In another post I was informed to search for listener information in this forum, because that is what I needed in order to attain the results I wanted.

In any event.... simply put, this is what I'm trying to accomplish. Upon opening my document I would like to execute a macro (which is already written and works) every time an event occurs in column I. I was informed I needed to open my document in “listener mode”... and have been searching for such information. Previously, I was trying to "call" the macro from an "IF" statement, but was informed that's not possible.

Thanks
OpenOffice 4.1.3 on Win10
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Opening a Document in Listener Mode Help

Post by FJCC »

The first Sub below loads a listener covering I1:I10 on Sheet1. That Sub needs to be called when the document is opened. That is set up with the menu Tools -> Customize and using the Events tab. When any cell in I1:I10 is modified, the Sub Modify_modified is called. In this example, it just prints a message. He can place your code there instead. The variable oEv will contain a reference to I1:I10, the thing that triggered the Listener. The last sub is just for housekeeping but should be included.

Code: Select all

Global Listener  
Sub LoadListener

Doc = ThisComponent
Sheet = Doc.Sheets.getByName("Sheet1")
Rng = Sheet.getCellrangeByName("I1:I10")
Listener = createUnoListener("Modify_","com.sun.star.util.XModifyListener")
Rng.addModifyListener(Listener)
End Sub

Sub Modify_modified(oEv)
print "In sub"
End Sub

Sub Modify_disposing(oEv)
End Sub
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
User avatar
brookc84
Posts: 27
Joined: Sat Dec 07, 2013 3:52 pm

Re: Opening a Document in Listener Mode Help

Post by brookc84 »

Thanks for the info, but I'm confused and not sure what to do with it. I feel like a moron. Do I replace the word “print” with the name of my macro in the second sub? Where and how do I enter the code?

When I open the document and click on “Tools -> Customize -> Events”, I see a chart with 24 event names which don't mean anything to me. I would assume I need to “cut & paste” the code you provided to some location. More instruction would be most helpful.

Thank You
OpenOffice 4.1.3 on Win10
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Opening a Document in Listener Mode Help

Post by FJCC »

To activate the listener when the document opens, go to Tools -> Customize -> Events, click once on Open Document and then click the Macro button at the upper right. This will open the macro organizer dialog. Expand the list on the left to navigate to the location of your code and choose the LoadListener Sub. In the image below it shows what the dialog looks like after I have chosen the Sub. My document is called Listener.ods and the macro is stored inside of the Standard library and the Module is called DictPractice (that strange name is leftover from another use).
macroOrganizer.JPG
Click OK and you will now see that the path to the macro is in the Assigned Action column next to Open Document. Click OK again.

To get the code you wrote to run, paste it between Sub Modify_modified(oEv) and End Sub so that it is the content of that Sub. That is, replace the entire print statement with your code.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
User avatar
brookc84
Posts: 27
Joined: Sat Dec 07, 2013 3:52 pm

Re: Opening a Document in Listener Mode Help

Post by brookc84 »

Thanks for the help, but I'm about ready to give up. When I incorporate my macro (which works 100% of the time correctly) into the Listener routine, it only partially works. I don't understand coding (I used Macro Recording to create the Macro), but I “cut & pasted” my macro code into the Listener routine and it simply doesn't work correctly.
OpenOffice 4.1.3 on Win10
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Opening a Document in Listener Mode Help

Post by FJCC »

If you post your file here, I can take a look at it. To post a file, click Post Reply and then look for the Upload Attachment tab just below the box where you type a response.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
User avatar
brookc84
Posts: 27
Joined: Sat Dec 07, 2013 3:52 pm

Re: Opening a Document in Listener Mode Help

Post by brookc84 »

Attached are 2 spreadsheets. The first one uses a macro (Update Handicaps) to perform a function whenever a number (usually between 15-32) is entered in column I. The macro copies Columns E-I into Columns D-H and then deletes the number in Column I. It works perfectly... enter a number in column I and 'click' on “Update Handicaps”.

The purpose of the second spreadsheet is to utilize a Listener routine to accomplish the same task, without the need to 'click' on “Update Handicaps” every time a number is entered in column I... simply move on to the next data entry in another row. The macro code has been placed inside the Listener routine.

Unfortunately, the Listener routine doesn't function as intended. It over moves some data and deletes other data. Difficult to explain, but just experiment with both spreadsheets. Thanks
Attachments
12152017ListenerTestGolfScores.ods
(19.96 KiB) Downloaded 216 times
12152017TestGolfScores.ods
(20.23 KiB) Downloaded 136 times
OpenOffice 4.1.3 on Win10
Post Reply