Ask Your Question

How to use a mouse click event in Calc? [closed]

asked 2017-09-20 19:32:20 +0200

Muldoonsghost gravatar image

updated 2020-08-04 17:56:54 +0200

Alex Kemp gravatar image


I wish to click on a cell and select it but when I do so I want some commands, a macro i guess, to run. I don't know which cell in a row or column is going to be chosen so in the on click event for the sheet i want to find which cell has been made active and work from there. Using Libreoffice Basic I can create the commands but I'm not finding a lot of information about how to use events.

I have found an Assign Action box hidden under the sheet name that seems to have double click as an action and I'll experiment with this but what about single click and other windows reported events? Is there a document that shows how this works? I have the 3.4 basic programmers guide but it doesn't seem to be in there.

If you can point me to a tutorial I would appreciate it. Thanks.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-08-04 17:58:24.303325

2 Answers

Sort by » oldest newest most voted

answered 2017-09-21 08:25:06 +0200

pierre-yves samyn gravatar image


Another example implementing the "sheet" events accessible through the context menu of a tab: SelChangedEvent.ods

Sub PysSelChange(oEvent)

if oEvent.supportsService("") then
    if oEvent.AbsoluteName = "$Sheet1.$C$3" then
        if oEvent.formula = "" then
            oEvent.value = now
        end if  
    end if  
end if

End Sub

The procedure receives the trigger event as a parameter.

  • We first check that the selection is a cell.
  • If so, is it C3?
  • If yes, is it empty?
  • If so, insert the time.

The example given by @JohnSUN is old, as he says. Previously we did not have access to sheet events and we had to manage listeners. They have the disadvantage of being deactivated during the "view" changes, e.g. after a print preview. Nevertheless, it is (as always from JohnSUN) a very good example and that sometimes the sheet events do not allow to do without listeners.


edit flag offensive delete link more


@pierre-yves samyn Thank you my friend. I also wanted to give an example with the events of the sheet, but saw the words "other windows" in the question. Perhaps I did not correctly understand this part of the question, but I decided that the "general approach to assigning an event listener" would be more useful.

JohnSUN gravatar imageJohnSUN ( 2017-09-21 08:34:24 +0200 )edit

Good morning and my thanks to you too. Here is a screenshot of your example on my machine and I have no reaction to clicking on the cell here either. I wonder if I have some functionality turned off?

I shall keep experimenting, thanks.

Muldoonsghost gravatar imageMuldoonsghost ( 2017-09-21 11:43:47 +0200 )edit

@Muldoonsghost Sorry I can not watch your video yet but the first assumption would be that you did not allow macros to run: ToolsOptionsLibreOfficeSecurityMacro Security (Medium is a good compromise)

pierre-yves samyn gravatar imagepierre-yves samyn ( 2017-09-21 12:09:00 +0200 )edit

@pierre-yves samyn. Thanks for the follow up, my setting was on High which I assume is default. Now I can dee your example working.

Returning to my original question, can you recommend a tutorial for event handling? I've tried searching online for "oEvent.supportsService", "oEvent" and "oEvent.AbsoluteName" and found some non related stuff. Where can I find a list of all oEvents?

I have just found a pdf called " Macros Explained - OOME Third Edition". This is my afternoon reading

Muldoonsghost gravatar imageMuldoonsghost ( 2017-09-21 13:42:13 +0200 )edit

This wiki page groups resources. I can not recommend a tutorial because I use the API documentation directly. However I think the OO.o Basic Guide website contains useful references, for example: Events

pierre-yves samyn gravatar imagepierre-yves samyn ( 2017-09-21 13:53:36 +0200 )edit

Thanks, more reading for me. I think this is now as solved as it's going to be. Your help has been invaluable.

Muldoonsghost gravatar imageMuldoonsghost ( 2017-09-21 16:00:33 +0200 )edit

How do I know what the type of oEvent is and what fields/function this type has? In other words: where is the documentation for that event?

Trismegistos gravatar imageTrismegistos ( 2018-07-16 14:24:48 +0200 )edit

Documentation? For example hereFYI I just clicked on the link Events from the comment @pierre-yves samyn above

JohnSUN gravatar imageJohnSUN ( 2018-07-16 14:54:42 +0200 )edit

answered 2017-09-21 07:31:14 +0200

JohnSUN gravatar image

updated 2017-09-21 08:05:19 +0200

I hope that this small example will help you get started - C:\fakepath\ClickOnSheet.ods (It was prepared several years ago and - surprisingly! - is still working today)

This macro changes background the cell from the A1:E5 range which is clicked. Colors change every five clicks red - yellow - magenta -green - light blue - dark blue - again red and so on in a circle (the color changes every five clicks). Clicking on cell F6 produces another action.

It all starts when you open a spreadsheet (see Tools - Customize - Events)

image description

edit flag offensive delete link more


Good morning. I tried your sheet but I couldn't see any reaction when clicking on the red cell. Here's a screen cap to show what I saw. Is this different from your expectation?

Thanks for taking the time to help me. I'm very grateful.

Muldoonsghost gravatar imageMuldoonsghost ( 2017-09-21 11:34:09 +0200 )edit

And now I see yours working too. Again thanks, I'm sure I'll cobble up what I want now. [Edit] Looks like I'll need to learn Russian too. :)

Muldoonsghost gravatar imageMuldoonsghost ( 2017-09-21 13:47:16 +0200 )edit

Question Tools

1 follower


Asked: 2017-09-20 19:32:20 +0200

Seen: 3,230 times

Last updated: Sep 21 '17