How to use a mouse click event in Calc?

[Muldoonsghost]

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.

[JohnSUN]

I hope that this small example will help you get started - 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)

[Muldoonsghost]

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?

2017 09 21 10 13 06

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

[Muldoonsghost]

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.

[PYS]

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

[JohnSUN]

@PYS 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.

[Muldoonsghost]

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?

2017 09 21 10 36 26

I shall keep experimenting, thanks.

[PYS]

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

[Muldoonsghost]

@PYS. 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

[PYS]

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

[Muldoonsghost]

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

[Trismegistos]

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?

[JohnSUN]

Documentation? For example here FYI I just clicked on the link Events from the comment @PYS above