Calc: Triggering a macro by (double-)clicking on a cell

English
#1

Conditional formatting is basically meant to apply a style to a cell based on its content. I agree, but it would be so useful and powerful for so many developers if the conditional formating window also offered the possibility to call a macro when a cell was (double-)clicked … I put the question (tdf#146613) to Bugzilla but they sent me back to this site … So, could somebody help me telling me how he would proceed to solve my following problem ?

My problem : I am confronted with the problem of having to copy into a target array of 9x9 elements the value of a cell chosen by (double-)clicking in a source array of 27x27 (=729) elements (the values of this array being themselves calculated (each containing a formula))(the address of the target cell being a function of the address of the source cell). It is equivalent to turning a source array into a range of tiny buttons. In my case, creating a clean and beautiful array of 729 tiny buttons (with calculated face value) is just crazy …

My “extended conditional formatting on each source cell” proposal to Bugzilla would have suit me perfectly (as many other programmers), but … At the moment, I have no idea how to solve this problem else than by programming a macro called via Sheet event > Modified content …

Thank you in advance for your attention to this request. Kind regards, JoVD.

===
Made the link to bugzilla work. (@Lupp)

#4

CF doesn’t apply the style in the sense of assigning it to the cell, but overlays it’s appearance/functionality to the respective (grid-)area in the view.

#7

Alternative solution
macro_url.ods (11.0 KB)

#8

Perfect solution. Please explain how the sURL argument is passed to the Main procedure from the hyperlink.
Sub Main(sURL)

#9

It is passed automatically without any additional tricks. Some day I found out because I called a macro expecting an event struct but got a string when calling by hyperlink.

#11

Yes, it’s not obvious. I take note of these things. And I give you an upvote.

#12

Thanks, but according to the meanwhile uploaded document, the solution does not fit well to this problem where the clickable cells already have some numeric formulas.

#13

I won’t downvote… I’d rather add another vote for @Lupp. And I’m adding your solution to my macro library…

#15

I can’t see how the soluion presented here can do that.
If a handler for the sheet event ‘onDoubleClick’ is a xxeptable, it can easily do anything related to the cell object. .

See:
onCellDoubleClickedExample_2.ods (20.2 KB)

#2

I can’t see a close relation to CF or anything of the kind, but you can use cell styles (their names) to control the action of a sheet event.
See attached example.
onCellDoubleClickedExample.ods (9.9 KB)
<edit: dt=" 2022-01-17 about 09:40 UTC">
I missed to kill the event with the help of the handler. The default handler therefore is called after exit from the Sub, and the cell having the focus at that time is entered in edit mode.
Since this may be expected to not happen, use the solution sketched in the new attachment.
onCellDoubleClickedExample_3.ods (22.3 KB)

</edit:>

2 Likes
#10

A very simple solution. Great.

#16

Very interesting and smart answer, Lupp. Still some problems to keep my special grid (in the source array).
Here is my solution inspired by Villeroy (see my macro function dc3b) :
LO_TestB.ods (20.2 KB)
Still a big thanks to you all.
JoVD.

#3

I don’t understand - why Content Changed? You want to handle double clicks, don’t you?
DblClick_Event

#5

To Lupp : Using pEvent.supportsService and pEvent.AbsoluteName seems to be a good track. Thank you.

To JohnSUN : As much for me, please read “Double click” instead of “Modified content” (or “Content changed”). Thanks for your help.

#6

Thank you for your onCellDoubleClickedExample.ods, Lupp.
Before I consider my question fully answered, may I ask for one last helping hand by correcting my dc3b macro in my attached LO_TestA.ods (20.3 KB) file so that when I click on a cell in my source table (3 boxes (27x9) on the right containing a number from 1 to 9), that number is copied to the corresponding cell in my target table ((9x3) in the upper left corner)?
(Yes, I am a beginner in LO programming) Thank you in advance.

#14 
r = pEvent.CellAddress.Row
c = pEvent.CellAddress.Column
v = pEvent.getValue()
sh = p.Event.getSpreadsheet()
cell = sh.getCellByPostion(c -x, r -y)
cell.setValue(v)

Complement the correct x and y for the target cell position

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.