Ask Your Question
0

How to use a mouse click event in Calc?

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

Muldoonsghost gravatar image

Hi.

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 close merge delete

2 Answers

Sort by » oldest newest most voted
1

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

Comments

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?

https://youtu.be/veT240vQ9ek

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
1

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

pierre-yves samyn gravatar image

Hi

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

Sub PysSelChange(oEvent)

if oEvent.supportsService("com.sun.star.sheet.SheetCell") 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.

Regards

edit flag offensive delete link more

Comments

@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?

https://youtu.be/O2l3vM_B9UI

I shall keep experimenting, thanks.

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

@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 "OpenOffice.org Macros Explained - OOME Third Edition". This is my afternoon reading

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

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
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 1,466 times

Last updated: Sep 21 '17