@sokol92: naming a function “MyMakro” is a bit stupid… isnt it? … why not for Example something more sensefull like “increment_cell”
@karolus, I think that the macro is educational in nature to demonstrate the technique of working with the double click handler. As for the name, you are probably right.
As you state its "educational" and therefore it should NOT provide bad naming-habits, because people tend to adopt bad conventions immediately!
OK. Great, that works fine. Thanks so much.
One minor thing. If you double click a cell, and it increments, the focus stays in that cell. Double clicking again does not increment that second, and subsequent, times.
So is there a way to remove focus of the cell - I suppose I mean take the cell out of edit mode - after the increment has happened. In preparation for a second execution?
I renamed the macro ‘IncrementCell’ by the way.
Right. Forget that last bit. It was because I renamed the macro but did not change the macroname=False and macroname=True statements to the new name.
Apologies.
Great solution. Thanks so much sokol92!!
1 Like
@sokol92: You may be intertested in the commented modification of your clever solution contained in the attachment. More flexibility without complicating the handlng is a ticklish problem.
incDecByDblClickDemoBased_sokol92.ods (23.8 KB)
1 Like
Hello!
I have been using this macro and it works beautifully. However, on some columns I am finding that the macro fails with the message as shown in the attached snip.
As I work I am adding rows and columns - I add rows for a new species being found, and I add columns for a new sample being analysed. I don’t know if this is what causes the macro to not work, but if so it is erratic, because I can add rows and columns and the macro works. I have not exceeded the range that the macro specifies, and there is a value in the cell - 0 usually - and the cell is formatted as numeric…
Can anyone please suggest why this is happening please.
Let’s be careful and before the line CellAddress= add new line:
If Not HasUnoInterfaces(oCell, "com.sun.star.sheet.XCellAddressable") Then Exit Function
OK. Thanks, excellent. Very good, that works and stops the error message, and the value of the cell does not change.
So why is that? I mean, what is HasUnoInterfaces doing here? Checking to see if the cell is addressable?
If that is the case - why is the cell not addressable? - what have I done? And how do I make it so?
So what I need to do here is find out why the cells are not incrementing, and fix it so that they do.
If oCell is a range of cells, then this object has no property CellAddress nor method getCellAddress().
With the HasUnoInterfaces function, we check that the object (oCell) that is the parameter of the double click handling function is a cell (has a getCellAdress method).
If you, for example, double-click a control object, an image, a Shape, etc. on a Calc sheet, the oCell parameter will not be a cell (let the name oCell not be misleading).
What specifically (not a cell) “clicks” in your case can only be determined by you. 
Oh, right, I understand.
But as far as I can see, it IS just a cell. I do not have anything else - visible, anyway - on the spreadsheet; no objects, boxes, images etc, buttons or controls. Just cells.
By adding columns or rows, might I have inadvertently created a ‘range’, or some other non cell object?
If you could upload a file and specify the sequence of actions for an unusual double-click, then the number of participants in the research would increase.
OK, point taken! I hope this works - or doesn’t, as the case might be.
In the attached spreadsheet, double clicking on any of the populated cells will increment them, except those in columns K and L
IncrementExample01.xlsx (7.5 KB)
1 Like
It occurs to me - and it has probably already occurred to you sokol92 - that if HasUnoInterfaces tests to see if the object being clicked is an cell, a test that in these cases fails, then can a similar function return what the object is? Which might provide an idea of what is going on here.
@robinlew Before we move on, can you explain why the file became XLSX? So far, all the examples in this discussion have been ODS or ODB. At what point and why did the file type change?
1 Like
I saved the spreadsheet right at the beginning as xlsx purely for compatibility - I don’t know anyone else who uses LibreOffice - so it was to ensure that they would be at ease and familiar with an xlsx file, whereas an ods might cause them to query, or not be able to open it, or something. I could just as easily be using ods for myself.
Robin
OK, I understand - the file type is “File Excel 2007-365 (.xlx)” so as not to confuse Excel owners. And in what module do you store the macro? In the screenshot with the error, only the macro code, no details are visible. And how is this macro activated?
The macro is stored in MyMacros - Standard - IncrementCellDblClck; the macro is named IncrementCellDblClck. I activate it by right clicking on the sheet tab, selecting sheet events, and then assigning this macro to the double click event.
Thanks for the clarification - now I was able to reproduce the error. Indeed, a double click, for example, on cell M2 passes a ScCellObj type parameter to the function (as expected).
And clicking on a cell in the K or L column passes the Shape object as a parameter - and this is not correct.
Suddenly. Obviously wrong. I see an error, but I can not understand the reason yet.
Could you - temporarily, for the sake of experiment - re-save your book in the ODS format and check if the error remains when using the native file format?
Update It’s hard to believe, but the comments to the cells in the previous column are to blame for the macro not working properly. Yes, comment pop-ups are not visible now, but they are there. And, apparently, double-clicking on a cell does not pass the cell to the function, but an invisible comment box.
2 Likes