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
Good result - thank you. After reading this I went back to the spreadsheet and, like in your screenshot above, the comment box overlaps parts of the cells in the 2 columns to the right. If I double click on the cell in column G, no increment. If I double click on the left side of the cell in column H, no increment. But if I double click on the right side of the cell in column H, the macro fires and the cell contents increment.
So I would guess that is a quirk of the software. And testing for the object in question is pointless because it wonât have the cellAddress property. So no solution programmatically.
But if I ensure I put a wide empty column to the left of the column I am working on, then that shunts the column I am working on and want to be incrementing across and out of the comment box area. I tried that and it works - the columns that would not increment, will if I do this.
Ideally I suppose when a comment box closes, the comment box object should be removed, rather than remaining albeit in ghost form.
Anyway - a big thank you. Great Result.
No matter which file generating application you are using (video editor, CAD, desktop publishing, office suite, whatever) you should always save your work in the native format of the application you are using. Excel can open ods files. However, Microsoft will never execute StarBasic code. This is where compatibility ends.
Yes, Villeroy, point taken, and I will save the spreadsheets, and keep them, as ods files. Thank you.
âŚbut it wonât get rid of the described error - you need to come up with an alternative to comments or completely abandon them, or always remember about very wide columns
Absolutely. The âvery wide columnsâ works fine, and is an easy solution. The fact is, I now know what the problem is and what is causing it. Thanks again
Hello to all of you who helped me so much in the past with this macro - it increments the integer contents of a cell on a double click.
Option Explicit
' On double click handler.
Function MyMacro(oCell) As Boolean
Dim cellAddress, v
MyMacro=False
If Not HasUnoInterfaces(oCell, "com.sun.star.sheet.XCellAddressable") Then Exit Function
cellAddress=oCell.cellAddress
If cellAddress.Column<=260 And cellAddress.Row<=999 Then ' A1:AA1000
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
I have been using it a lot, but not for the last 6 or 9 months. Now I come back to using it and it works for a short while - say 10 minutes - and then stops working. The only way I can reactivate it is to close the file and reopen it. I reduced the size of the spreadsheet a lot because I thought it might be a memory issue, but it still stops working after a while. I attach a copy of the calc spreadsheet as well.
If anyone can figure out why it might be failing I would be really grateful.
Just to add, I am on the same laptop as before, but it is a Lenovo Thinkbook and I have had some weird problems with it - like audio stops working every so often - not new things, but things to make me consider changing it. But after two years Iâm still using it. That is probably irrelevantâŚ
Chironomid Analysis Spreadsheet Small Copy.ods (29.3 KB)
Your attached example tries to call the handler from your local profile.
Users wanting to try the included Basic routine as intended need to change the handler for onDoubleClick via the context menu of the sheetâs tab.
Also1: A malfunction as described here should be suspected to be caused by a bug of the used (just upgraded?) version of LibO or by a strange/rare variant of user profile corruption. Contributors of this site will then find it very difficult to guess the precise cause.
Also2: The way you restrict the range to apply the handler (cellAddress.Column<=260) isnât recommendable. I would suggest to use a cellstyle name (or part of it).
Hi Lupp,
Thank you for this
So this is shameful, I know, but I do not know the deeper workings of LibreOffice.
Could you explain a bit further about the local profile - as opposed to ? global? profile?.
I have copied the spreadsheet with the original macro and changed it, several times, depending on what I have used it for. But this is the first time I have come back to it after several months.
When I copied the spreadsheet to a new location, sometimes the macro code was not present, and so I would create a new macros and copy and paste the code from one to the other. I was never sure whether the code should reside under
My Macros & Dialogs| Standard | Module 1
or under
Spreadsheet Name | Standard | NewMacroName - in the spreadsheet I posted this macro is also placed here and called UpdateCountInCell
I tried associating each of these with the sheet double click event, but they both had the same problem.
Would the âMy Macros & Dialogsâ be the âglobalâ repository, and the âSpreadsheetNameâ the âlocalâ?
I do not doubt, but I do not know, that there has been an LO update in the intervening time. My current version is
Version: 7.3.5.2 (x64) / LibreOffice Community
Build ID: 184fe81b8c8c30d8b5082578aee2fed2ea847c01
CPU threads: 12; OS: Windows 10.0 Build 19044; UI render: default; VCL: win
Locale: en-GB (en_GB); UI: en-GB
Calc: CL
Would it be possible for me to rollback to a previous version and see if that fixes this problem?
I will also try this spreadsheet today on a different laptop to see if that is the problem.
Best regards
Robin Lewando
This is one of the questions I have âinternal troubleâ with.
Whether 'tis cleverer in reality to do things as need comes or to prepare against a sea of cases and sue to experience succumb âŚ
A âperfect solutionâ for incrementing plain integers by one may be of marginal use in a very special case. Preparing it for somebody not capable of finding a solution himself is a rarther dull thing. And the next questions may be âhow decrement?â, âhow by 10?â, âhow increment a time by one hour?â and a little million more.
A few years ago I analyzed the GeneralIncDecProblem to more detail and ended up with a very useful solution for ânext toi everythingâ, but personally I donât uise it. I failed to memorize and to neetly apply all the finger-twisters needed to make that solution as useful as expected.
In case users here are much better in finger-twisting and memorizing, I attach the âfinal versionâ I got in spring of 2020 (slightly improved a few months later) when I was badly damaged and bored therefore.
Anybody may make his fortune of it.
The solutions are based on user code which is contained together with examples and explanations in the attached file.
ask233471_IncrementDecrementByModifiedToolbar20210909.ods (31.6 KB)
1 Like
Greetings @Lupp!
Thank you for, as always, interesting content. Iâll definitely check it out when I get a chance.
By the way who came up with the trick to define a Calc document:
oDoc=oSheet.Drawpage.Forms.Parent
I didnât think of it myself. Excellent! 
Dont know for sure. I was teached by @Villleroy insofar. He may have invented it.
@Lupp, yes, I remembered this topic - Create floating button to add values to cells in Calc.
By the way, @robinlew, you might be interested in the solution from that thread. It works not from double click, but from two clicks - select the cell and increase the value. The number of movements is the same, but there is less chance of an error (double-clicking on an adjacent cell).
1 Like