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
Thanks @JohnSUN, that’s a brilliant solution.
I like the double click well enough - it works, it is simple, but you are right - there is a large possibility for making a mistake in double clicking the wrong cell, or even double clicking twice. And no going back, except by overtyping.
The floating button is great too. Adding (or subtracting) more than one is more than I need for this task. So I tried this floating button bar solution but I cannot get that to work in my spreadsheet. Do I need to create the button bar or define it, or the shapes, somewhere?
It might all seem a lot of work but these two simple solutions make an enormous difference when your face is stuck to a microscope and you need to register things as you see them, up to the hundreds, so these small solutions have usefulness way beyond their simple appearance and concept.
To be honest, in the 21st century, I would think about voice input for this situation - you look, say out loud, what you see, the machine registers … Or image recognition: I photographed an image from a microscope, gave it to the machine - let it count.
Put the example sheet into edit mode
Select a group of buttons, copy and paste anywhere on your sheet. Disable edit mode.
Voice recognition and image recognition are fine in theory and you would think would be accessible and produce high quality and dependable results. But they aren’t and don’t, so we need to use what we have. And the simplest is often - usually - the safest bet.
As for copy and paste the button group - I’m flying blind here. I am new to basic scripting in Calc.
A Scripting Framework error occurred while running the Basic script vnd.sun.star.script:Standard.setNewValue.onBtnClick?language=Basic&location=document.
Message: The following Basic script could not be found:
library: ‘Standard’
module: ‘setNewValue’
method: ‘onBtnClick’
location: ‘document’
Yes, grand job! Thank you!! All working now. I understand how it all hangs together much better now - good exercise.
Thanks everyone for the help and advice.
It’s common knowledge that Chuck Norris counted to infinity, but few people know that he started with negative infinity.
It’s common knowledge that control buttons, toolbar areas, and links from text can call routines in LibreOffice using the protocol vnd.sun.star.script. It’s less common that also cells have a property .Hyperlink . I spent some time to consider if this kind of links can be used to manage the incrementing of cell contents (e.g.).
Result: Yes, it can be done, but there are lots of ovehead, and the shortcomings/problems are severe.
Somebody who would like to study this in detail may tell me. I can provide my demos then.
PM preferred in this case.
Yes, it works. But initially this solution was intended for a person who, with a tablet in his hands, registers the fish catch of the competition participants. That is, the angler pulls out some kind of fish and reports the type of fish and the number, and the registrar promptly increases the score of this competitor in the corresponding cell. (In any case, this is how I understood the description of the problem). So, the solution is intended for an operator whose sensor (ears) and manipulators (eyes and hands) are separated. In your case, the situation is a little more complicated - the sensor (eyes) is already taken… That is, you have to click the mouse at random or look away from the eyepiece of the microscope to see what exactly you are clicking on the computer.
It seems to me that another solution should be sought.
In your question, you talked about the range of cells A1:AA100, which is 2700 different counters. Do you really need to count that many different objects? Not? What is the real number?
To be perfectly honest I find the double clicking method works best. There is an inherent problem - which I have no doubt can be easily solved - that the floating toolbar overlies a cell - which might just be the next cell the user wants to operate on. Whereas the double click solution goes straight to the cell. This solution could maybe do with some facility to decrement by 1, just in case of error.
It is not a bad thing to relax the eyes by just transferring the focus from the microscope out to the computer and then back again. The eyes can get very tired looking down a microscope for long periods.
That many cells would not be necessary and as you say that really is far more than would normally be needed. But it is possible to have as many as 30 species in a sample. And if we allow different columns for different samples, and each sample may have a different set of species, it might be that we would end up with 50 species in total, as a maximum. I would say normally we would not expect more than 25 to 30 - especially if we are counting various types of spores, diatoms, testate amoebae and the occurrence of charcoal, phytoliths, as well.
If I am looking at samples from every 1/2 cm of a core, or maybe a set of repeat counts from the same sample, then I like to have them alongside each other. Which is unnecessary and throws in the possibility of adding counts to the wrong column. But I make sure that doesn’t happen…
I know it all sounds primitive, but actually I have more faith in humans keeping control of actions, than technology, in certain situations anyway. Which I am sure might annoy some people on this forum, for which I apologise. It’s just my opinion, of course.
Indeed, that topic discusses the same problem with spreadsheets as poor database surrogates. The database demo I’ve uploaded last week demonstrates how to maintain an inventory with smallest possible effort while having full overview on how many of which items have been added or removed at which time while having a lot more choices regarding the user interface.
As far as registering the count of different types of micro fossils while looking through a microscope, the best solution as far as I see it is the simple incrementing of a cell in a spreadsheet.
Image recognition has been and is being tried, but pollen grains and spores do not always show their most distinguishing features, shapes etc, being bent, folded, damaged, at a funny angle and so on, and are also subject to some variation, so really there is no substitute for the eye of the experienced researcher. What you identify is also subject to a knowledge of the environment being looked at, and this can be a major factor in deciding on an intelligent identification in some borderline cases. Image recognition works best for diatoms, but if you are looking at a whole set of various types of microfossils, as well as charcoal, micro tephra, all sorts of other things, then it starts being a serious problem.
Saying the count is possible, as in voice recognition, but it would be necessary to say the identification and then the count (which would most often just be one), so the operator would have to say the right word in the right way - how flexible is voice recognition? But then there are issues with extraneous noises, coughs, sniffles, somebody else speaking. I have tried using a speech to text software for my deaf mother and we runs into problems with it quite quickly. Which could all be overcome, but since there is not an affordable and accessible and reliable implementation of this, it is not an option.
And the database is an attractive option, conceptually, but it is rather a sledghammer to crack a nut solution.
It really is a matter of what the task in hand actaully requires, and so for me, the double click increment works just fine.
Open my database and the table “Articles” therein.
Replace the fruit names with fossile names (up to 50 characters, can be increased).
Remove all records from tables “Input” and “Output”.
Open form “Input”.
Select the first fossile name you want to count and then focus the numeric field.
Hit the upward arrow while counting.
Hit Tab, select the next fossile name, focus the numeric field and continue counting.
You may prefer the “Edit” form, select a fossile name on the left select the last empty “Quantity” box in the last row of the green grid. The arrow keys navigate within the grid but a latching mouse wheel works well. You may also place the mouse cursor above the up arrow, click on the left button of your touchpad while counting.
The simplistic user interface can be modified in many different ways. This demo assumes ingoing as well as outgoing items with no complicated macro code. Some kind of push button on a upward counting form would require a very simplistic snippet of macro code.
Have a look at the new input form.
UpCounter.odb (29.0 KB)
This document includes 2 little macros in StarBasic and Python.
As this topic got a revival I want to append: I meanwhile actually use a version of the mentioned solution myself. I needed to enhance it a bit for an additional purpose, and to simplify it with different aspects.