Apache OpenOffice Community Forum

Sorry to post about something so fundamental, but I have been searching without success.
I wanted a very simple operation to update a couple of cell values when I choose e.g. by clicking in a cell in that row.
I will replicate this functionality across about 1000 rows, so the row will be an arg.
I had assumed I would use a push button, but I found recommendations to use a hyperlink, that's fine too.
(It was quite hard to find docs about coding that custom operation, because most information is about "function" which is defined as something that returns a value (which my code will not) and "macro" which is defined as a recording of a series of user actions. Mine is neither. I eventually did find some documentation on OO Basic that defines a "procedure" (identified in OO Basic by "Sub") which seems exactly what I want. I'm a programmer so I'm happy to study further to write that code; that's not my problem.)
My problem is how to call the OO Basic procedure from a Calc cell.
That seems a very trivial thing, but I can't see how to do it. There are a couple of helpful looking examples of hyperlinks with references to sun star etc followed by some kind of local code item identity - I doubted whether I needed the sun star thing but I tried it - Calc complained.
I have created a test procedure, stored in the document, by using Tools | Macros | Organise Macros | OpenOffice.org Basic, and can see the procedure (let's say it's called DoFoo) listed under MyMacros | FooMacros (let's say that's what I called the collection when I created it) | Module1 (must be some default because I didn't name that or even see that name until I browse after writing the procedure), as an "Existing macro" (so I guess a procedure is a macro after all, despite it not matching the documented definition of a macro.)

So I would guess my cell has to say something like =HYPERLINK(<some path corresponding to the structure above>) but I have not been able to find how to construct that link. Just guessing =HYPERLINK(DoFoo) results in #NAME?
I set macro security to run after warning and set the folder with the document to trusted so a macro can run - if I can call it.
Hopefully this is an easy one...

See XMouseClickHandler in "Useful Macro Information For OpenOffice.org" By Andrew Pitonyak

Thanks, that looks like a very useful document that I had not found by searching around.
The idea of using a mouse event handler to get a macro run from a click in a cell seems not ideal because:
- it will give me X and Y coordinates which I assume are pixels which I'd need to convert to a row and cell - I imagine that is possible by interrogating properties of rows, but quite involved.
- it will be triggering all the time as I do other things in other cells - I can ignore them by working out whether the click was in a relevant cell, so it might not matter, but it's not pleasing.
I would still guess there is a more direct solution which I will continue to look for.

I tried the alternative search strategy of finding out how to do it in Excel, then searching for an equivalent in OO Calc. In Excel it seems that you do have to write an event handler (Worksheet_SelectionChange) that tests to see if the cell is relevant.
The thread https://forum.openoffice.org/en/forum/v ... hp?t=81565 is about whether there is an OO equivalent. The thread focuses on how to work out the row once the handler is triggered, which seems quite simple (and is not done by converting the mouse position of course, that was a red herring I followed because it appeared in the XMouseClick example), rather than how to get into the code in the first place.
For that I can't see anything simpler than the XMouseCliclHandler approach JeJe suggested (thanks again). Unless I get stuck I'll mark as solved...

please start from zero and tell whats the goal and NOT half the way how you think you may do it, thanks!

thiscomponent.currentselection.celladdress gives the sheet, cell and row

Edit: if you use MRI you can easily discover what's available to you.

https://extensions.openoffice.org/en/pr ... ction-tool

Wording my own way the general question as I understand it, I would get "How to pass parameter values (arguments) to subroutines?"
One use-case more or less described in the question is that the argument should be the row (its number?) where you just clicked on.
For this special case a sheet event is available (say 'onSelectionChanged'). If the handler needs additional arguments, it will depend on the circumstances in what way you pass them. The conversation is opened anyway.
The more general way to use a mouseclick handler was already mentioned by JeJe.
I personally would also consider to use the way parameter values are passed via the internet: By the query part of a hyperlink (URL).
I did this successfully ins some cases, and have a few helper routines for the task in my toolboxes. The link for a call to a basic routine with arguments would then look like
vnd.sun.star.script:myLibrary.myModule.mySub?language=Basic&location=document&myArg1=-14&myArg2=2022-11-26#
vnd.sun.star.script: is the mandatory part (protocol), and the arguments given as "language=" and as "location=" are obligatory. All the rest is arbitrary within the syntactical limits of respective RFC, and you only need to evaluate the equations.
In some cases I also modified the working of onSelectionChanged by formal query parts given by the "names" of cell styles.
Links calling a route planer with lots of pairs of geo-coordinates are my way to archive motorbike tours... and sometimes I composed such links (URL) in spreadsheets.
It really works!