How to increment a calc cell value on mouse double click?

[robinlew]

I wish to be able to increment an integer value of a specific cell by 1 when I double click on that particular cell, and I want this function to be available for every cell within a specified range, say A1 to AA100.
Now I understand that I can make the Action Assignment for a specified sheet, by right clicking on the sheet tab at the bottom of the spreadsheet and selecting ‘Sheet Events’, but how do I

1. access the current cell
2. test that it contains an positive integer, including 0
3. if it does, then increment that integer by 1
   I suspect that this is a simple enough macro, but it is accessing the cell, and the value in that cell, in which the double click occurs, that I do not know how to code.
   Thank you

[Villeroy]

t81615.ods (13.6 KB)

[robinlew]

Hi,

No, I thought of using spin buttons or just buttons, but I have columns of cells that I need to increment as I am counting, so I would need to have a button for each one, and also then create a new button every time I find a new type of thing that I am counting.
And then I have several columns - it is actually pollen grains as I scan over a microscope slide, with counts against each type, and new types added as I find them. Then a different column for a new sample.
So buttons are not really very practical.
Thanks anyway

[Villeroy]

https://ask.libreoffice.org/uploads/short-url/mmlXkQVp3JynB5mLZ2ynx2WoNrm.odb
As many boxes as needed.

[sokol92]

Let’s try:

' On double click handler.
Function MyMacro(oCell) As Boolean
  Dim cellAddress, v
  MyMacro=False
  cellAddress=oCell.cellAddress
  If cellAddress.Column<=26 And cellAddress.Row<=99 Then   ' A1:AA100
    If oCell.CellContentType=1 Then                        ' 0:empty 1:number 2:text 3:formula
      v=oCell.Value
      If v>=0 And v=Int(v) Then                            ' integer value >=0
        oCell.Value=v+1
        MyMacro=True                                       ' done
      End If
    End If
  End If  
End Function

[robinlew]

Hi

No, that doesn’t work unfortunately - unless I did something wrong…
Do I need to compile it? I assumed I did.
And I created it in ‘Standard Macros’.
Then assigned it to the worksheet to the double click event.
After assigning that macro when I double click I just end up being in the cell I clicked on, in edit mode

[sokol92]

Please, see attachment (Enable Macros).
DblClickDemo.ods (9.8 KB)

[karolus]

It works after you enter an initial number => 0 first

[karolus]

@sokol92: naming a function “MyMakro” is a bit stupid… isnt it? … why not for Example something more sensefull like “increment_cell”

[sokol92]

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

[karolus]

@sokol92

educational in nature

As you state its "educational" and therefore it should NOT provide bad naming-habits, because people tend to adopt bad conventions immediately!

[robinlew]

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.

[robinlew]

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!!

[Lupp]

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

[robinlew]

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.

[sokol92]

Let’s be careful and before the line CellAddress= add new line:

If Not HasUnoInterfaces(oCell, "com.sun.star.sheet.XCellAddressable") Then Exit Function

[robinlew]

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.

[Villeroy]

If oCell is a range of cells, then this object has no property CellAddress nor method getCellAddress().

[sokol92]

@robinlew

why is the cell not addressable

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.

[robinlew]

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?