How to set up a link to launch a macro?

[Mirador]

I know this question has been asked before and I have so far discovered 3 methods, but unfortunately, neither of them work properly.

1. Set up an ‘button’ using a rectangle object and assign a macro to it.
   Works, in that it will launch the macro, but the assignment is only valid while the document is still open. When the document is closed and re-opened, the assignment is lost, even when the document is saved.

2. =HYPERLINK
   Easy enough to set up, but doesn’t seem to work. I have set up the hyperlink as follows:
   =HYPERLINK(“vnd.sun.star.script:Standard.Module1.Email1?language=Basic&location=document”,“E-mail”)
   This creates a link labelled ‘E-mail’ in a cell, but the link does nothing - and yes, I am doing Ctrl-click.

3. Hyperlink icon
   I put the above in target and ‘E-mail’ as name, but again, clicking on it does nothing. I can even select ‘Button’ and it creates a nice button, but still does nothing when clicked.

Do I have the path wrong? How does one specify the path then the macro is located under the document name tree rather than the ‘My Macros’ tree?

In addition to the above, is it possible to have the cell that the link/button is in to get focus - or somehow get the row that the button is in - so that I can then base the action on the data in the current row?

Finally, how do I save the macro so that it is included in the spreadsheet document, so that when I move the document to another PC, the macro is still there and I can run it?

Sadly - and please forgive the rant, but it is borne out of sheer frustration - for whatever reason the organization of macros seems to be unnecessarily complex, confusing and it seems, buggy, in LibreOffice. For example, yesterday I had to re-write from scratch some three hours worth of work when I tried to move a macro from one location to another in the LO macro hierarchy and it completely vanished. There is no drag and drop or copy paste, so I had to create a new module and subroutine with the same name in the alternative location and copy and paste, at which point LO crashed. On re-starting the document was recovered but the macro was missing from both locations.
There seems to be no import/export macros either, although I guess one can copy and paste into a text document. As an example of confusion, Tools | Organise Macros | LibreOffice Basic - so we are presumably in the organsier, then there is another Organiser button under this? - Why are these features in a separate dialogue? Why are existing modules not shown on this dialogue? Why can one not re-name modules? Is it possible to put in a polite feature request to have the macro organizer overhauled and streamlined please?

[Lupp]

There are bugs, of course.Many just were and got fixed. No fix will apply to an older version:
Version information is essential if bugs may need to be discussed.
Some bugs only apply under a specific OS/version. Information is essential.
I don’t understand your number 3.
The complaints under 1., 2. I cannot confirm. Both the methods work for me and this persist save/load.
(LibreOffice 6.0.3 under Win 10)

[Lupp]

(Whining won’t help in any way.)

Quoting @Mirador: “… Tools | Organise Macros | LibreOffice Basic - so we are presumably in the organsier, then there is another Organiser button under this? - Why are these features in a separate dialogue? …”

Things are as they emerged from the evolution of the software. That’s the case with any software, with national and international law, and with our physical nature.

Quoting @Mirador: “…Why are existing modules not shown on this dialogue? Why can one not re-name modules? Is it possible to put in a polite feature request to have the macro organizer overhauled and streamlined please?”

You won’t get a better answer on your “why” most likely than mine above.

The existing modules are shown in the BASIC Editor/IDE and in the organizer as well, of course. You can also rename and add modules in the IDE without the organizer under recent versions, and the organizer can well move and copy modules by drag’n drop. There is no specific feature of the kind for single Sub/Function. Just Copy/Paste with common means.

Concerning OpenSource software you have a chance to get an enhancement if you report/suggest to the proper site: bugs.documentfoundation.org.
Try to word your request in a clear and concise way. No whining. (You aren’t a customer, but a user.)

[Ratslinger]

Hello,

There is a lot in your detail which is not really applicable to the actual question.

Because there is a great deal of information of which a discussion here would be just a duplication, I refer you to the documentation.

Here is the location for all LO documentation → Documentation/Publications.

Running a macro can be done from a Push button control ( sample attached ), a menu item or a toolbar item.

For more on this here is a link to the relevant on line documentation → Form Controls.

Sample - ButtonMacroDemo.ods Selecting the button on the sheet will execute the email macro discussed in a previous question of yours.

As for macros & the IDE, it is all relatively easy if you know its’ construction. Also, copy & paste of macros works with the actual code. It’s used all the time. Again, here is a link to some on line documentation regarding this: LibreOffice Basic IDE

You can also find more information on Forms (and their controls) and Macros (and organization) in the documentation regarding Base. Forms and macros are common in LO.

If this answers your question please tick the (upper left area of answer). It helps others to know there was an accepted answer.

[Regina]

You have likely missed, that you first need to add a module for your macro to the document, if you cannot save the macro inside the document.
While your document is open, select the document in the Basic-Organizer and then “Standard” in its tree, then press button “NEW”.

Another possible error: When you have selected the macro in the “Assign macro” dialog do not click OK immediately, but you have to click on the “Assign” button to really assign it.

Besides Hyperlink-Function and Form-Controls, or using menu or toolbar, there are other ways to trigger a macro:

(1) Using a self-defined Basic Function, that calls the macro. You can use the function =CALLMACRO(COLUMN();ROW())
with e.g

sub SayHello
    msgbox "Hello"
end sub
function CALLMACRO(byval column as string, byval row as string) as string
    SayHello
    CALLMACRO=column+"|"+row
end function

Each recalculation, e.g by F9, would trigger the macro.

(2) As error-action of a cell validity. Entering a “wrong” value into the cell would trigger the macro. (Not very useful here, but it works.)

(3) Assigned to an image. Insert an image. Then you get the item “Assign Macro” in its context menu. After you have assigned the macro, a simple click on the image triggers the macro. To edit the image, select it with right-click.

(4) Using listeners. But that is advanced Basic and you need to read the documentation already mentioned.