Page 1 of 2
Create floating button to add values to cells in Calc
Posted: Thu Sep 02, 2021 9:23 am
by M.Teich
To simplify data entry in the field, i.e. using tablets, I would like to create buttons in a spread sheet that will add or subtract values to or from the selected cell. Using spin buttons is not an option, as I would have to add hundreds of these buttons. This answer
https://superuser.com/questions/1307146 ... -reference shows exactly what I'm looking for, i.e. a single set of floating buttons that will move relative to the selected cell:
Unfortunately, VBA-macros will not run in an android environment, so I need to find a solution in a StarBasic based software, e.g. LibreOffice. I'm aware that questions related to porting VBA to SB are not very popular, but my programming experience is solely limited to R and I'm at a loss on how to solve this. I've boiled down the VBA-code to the bare essentials:
Code: Select all
Sub Worksheet_SelectionChange(ByVal Target As Range)
With ActiveSheet.CommandButton
.Left = Target.Offset(, 2).Left
.Top = Target.Offset(0).Top
End With
End Sub
Sub CommandButton_Click()
ActiveCell.Value = ActiveCell.Value + 1
End Sub
Any help in porting, or creating a new SB-macro from scratch will be much (!) appreciated.
Re: Create floating button to add values to cells in Calc
Posted: Thu Sep 02, 2021 10:51 am
by JeJe
Which cell is selected is already indicated. Why not just have two buttons in a toolbar?
Re: Create floating button to add values to cells in Calc
Posted: Thu Sep 02, 2021 10:57 am
by Villeroy
This is trivial to do with a database and a form.
Re: Create floating button to add values to cells in Calc
Posted: Thu Sep 02, 2021 11:09 am
by M.Teich
JeJe wrote:Which cell is selected is already indicated. Why not just have two buttons in a toolbar?
For ease of use in the field it would be most useful to have the buttons "floating" close to the activated cells.
Re: Create floating button to add values to cells in Calc
Posted: Thu Sep 02, 2021 11:12 am
by M.Teich
Villeroy wrote:This is trivial to do with a database and a form.
I'm sure it is, but unfortunately not to me. Also, it makes my life easier to stay within the semi-compatible world of Excel and Open/LibreOffice to move data between users/systems.
Re: Create floating button to add values to cells in Calc
Posted: Thu Sep 02, 2021 11:22 am
by Villeroy
Instead of moving data, any professional solution would store data on a database server.
Re: Create floating button to add values to cells in Calc
Posted: Thu Sep 02, 2021 11:34 am
by M.Teich
Villeroy wrote:Instead of moving data, any professional solution would store data on a database server.
I could not agree more, but we're literally stuck in the dark ages here and moving from pen and paper to a tablet and spread sheet would already be a significant improvement without spending a lot of resources. If I can convince people that digital is the way to go, then we can think about more professional solutions. I'm just a biologist and self taught R user/wannabe data scientist not a trained programmer. Therefore some simple StarBasic buttons will do just fine for the moment.
Re: Create floating button to add values to cells in Calc
Posted: Thu Sep 02, 2021 11:42 am
by JeJe
If you anchor a form control like a spin button to the page you might be able to write some complicated and probably dodgy code to position it on the Sheet selection changed event.
What about two keyboard shortcuts instead?
Re: Create floating button to add values to cells in Calc
Posted: Thu Sep 02, 2021 11:52 am
by robleyd
You might consider reading
Post within our Policies and Terms of Use and edit your post accordingly. The pejorative term you used in relation to
people actually applies to a number of volunteers on this forum, including those offering advice here.
Re: Create floating button to add values to cells in Calc
Posted: Thu Sep 02, 2021 11:52 am
by M.Teich
JeJe wrote:If you anchor a form control like a spin button to the page you might be able to write some complicated and probably dodgy code to position it on the Sheet selection changed event.
What about two keyboard shortcuts instead?
I'm working with a largish spread sheet on a tablet standing in a river - having an external keyboard is not an option and using the digital one reduces visible screen size even more. In VBA my preferred solution is 9 lines long and I can easily modify that code to incorporate more buttons if I should need them. Can the equivalent solution in StarBasic be that much more complicated?
Re: Create floating button to add values to cells in Calc
Posted: Thu Sep 02, 2021 12:00 pm
by JeJe
M.Teich wrote: Can the equivalent solution in StarBasic be that much more complicated?
Afraid so...
Re: Create floating button to add values to cells in Calc
Posted: Thu Sep 02, 2021 7:49 pm
by Bidouille
Without macro and with spin button controls
Re: Create floating button to add values to cells in Calc
Posted: Fri Sep 03, 2021 2:23 am
by JeJe
The attached moves a spin button location to the selected cell.
Re: Create floating button to add values to cells in Calc
Posted: Fri Sep 03, 2021 6:53 am
by Zizi64
JeJe wrote:
Which cell is selected is already indicated. Why not just have two buttons in a toolbar?
For ease of use in the field it would be most useful to have the buttons "floating" close to the activated cells.
You can assign the StarBasic macro to the floating menu of the cells in the LibreOffice. Check it in the Customize menu. (As I remember it: the floating menu of the AOO is not customizable.)
Re: Create floating button to add values to cells in Calc
Posted: Wed Sep 08, 2021 10:13 am
by JohnSUN-Pensioner
Ok, try this option
Re: Create floating button to add values to cells in Calc
Posted: Wed Sep 08, 2021 11:37 am
by JeJe
JohnSUN-Pensioner - that's good! Vastly better than my embryonic attempt.
Form controls have a habit of not being redrawn properly. Clicking a few times the leftmost control disappears occasionally... might need to force a repaint.
Re: Create floating button to add values to cells in Calc
Posted: Wed Sep 08, 2021 11:43 am
by JohnSUN-Pensioner
@JeJe Yes, I saw that sometime it's not visible. Everything is fine in LibreOffice Calc, in OpenOffice there is a problem. I didn't bother trying to fix it. Firstly, the button is there anyway (just click on the white square where it should be and it will work and become visible). Secondly, this leftmost button is responsible for subtracting 10, which means it will almost never be used.
Re: Create floating button to add values to cells in Calc
Posted: Wed Sep 08, 2021 12:08 pm
by JeJe
Code: Select all
thiscomponent.currentcontroller.componentwindow.invalidate(0)
Seems to work at the end of the ShowOrHideGroup sub.
Re: Create floating button to add values to cells in Calc
Posted: Wed Sep 08, 2021 12:45 pm
by JohnSUN-Pensioner
Yes, that's right, it will work. Just in the form
Code: Select all
ThisComponent.getCurrentController().ComponentWindow.invalidateRect(oBtnGroup.FrameRect, com.sun.star.awt.InvalidateStyle.UPDATE)
(no constant 0 in constants group
InvalidateStyle)
and not at the end ShowOrHideGroup sub but after the line
in onChngSelected sub (no need call .invalidate() for full window each time)
Good point. @M.Teich, please consider this change in your project
Re: Create floating button to add values to cells in Calc
Posted: Thu Sep 09, 2021 3:01 pm
by M.Teich
@all: thanks for the support - this is much appreciated. Thanks in particular to @JohnSUN-Pensioner: I will mess around with the code and see if I can do some minor customisations to suit my purpose, but it should work just fine.
Re: Create floating button to add values to cells in Calc
Posted: Thu Sep 09, 2021 4:35 pm
by Lupp
I never used LibO on Android (and don't expect to be forced one day). Therefore I don't know for sure that what I sketched would work there.
My intention was to create an alternative not needing buttons. There are "fundamental" reasons, but also there is the already mentioned "invisible-button-bug" occurring now and then without any reproducibility. Using links in place of buttons would avoid the problem.
The attached example document may mainly be useful for visitors looking for an alternative concept. Please have in mind, that URL wth the vnd.sun.star.script protocol accept addtional parameter equations appended to the mandatory query part.
Re: Create floating button to add values to cells in Calc
Posted: Thu Sep 09, 2021 4:56 pm
by JeJe
If any visitors don't realize form control buttons can be set to repeat and the delay set in ms so you don't have to press repeatedly, just hold the button down.
Edit:
And another approach would be a non-modal dialog instead of form controls - where there wouldn't be repaint issues.
Re: Create floating button to add values to cells in Calc
Posted: Thu Sep 09, 2021 8:03 pm
by eeigor
JohnSUN-Pensioner wrote:Ok, try this option
@JohnSUN, @Lupp, both examples are good. I am taking them into service. No problems are observed in LO (Linux).
And if you want the buttons to be anchored to the width of the cell, then add the code...
Code: Select all
Sub SetAnchorToCellResize(cell, shape, resizeWithCell)
With shape
If resizeWithCell Then
.Anchor = cell
.ResizeWithCell = True
End If
.setSize(cell.Size)
.setPosition(cell.Position)
End With
End Sub
Re: Create floating button to add values to cells in Calc
Posted: Thu Sep 09, 2021 11:54 pm
by Lupp
Sorry. I didn't study every detail of this thread before I posted my suggestions.
Somehow related:
On 2020-03-13 there was a question in a different community site, and since I wasn't fully mobile at that time, I felt a bit bored, and took the time to design a solution.
You may visit the mentioned topic:
https://ask.libreoffice.org/t/add-short ... -day/50306.
On 2020-07-08 there was another attack of boredom, and I made an enhanced version including additional information.
Based on this version I just did a little bit of superficial updating, and you find the result attached here:
Please don't miss to read:
Already the first version accepted as a fact that there are no "floating buttons" and that a workaround implementing them is possible (based on a
onSelectionChanged handler), but rather complicated: much ado about little functionality.
Since "default" incrementing / decrementing isn't the same for everybody in every case, and since breaking boredom by programming requires a credible challenge, I first considered the desirable flexibility. This lead to some decisions:
- Shortcuts aren't an option because they (1)cannot be defined for a document, and (2)too many would be needed.
- Even "floating buttons" wouldn't be good. Again too many needed, because no sufficient modification is supported.
- The sensitive element must be a toolbar (floating probably) therefore.
- The functionality can then be modified by using the keys rightfully called
modifiers.
-
The stepwidth should depend on the NumberFormat of the addressed cell. (No sense in incrementing a date by 100 days, e.g.)
Visitors considering to use a floating toolbar stored to the document should know that the
floating may need to be re-eastablished after opening the document in a different environment. The attached example was stored with the toolbar shown in the merged area Q3:S3.
Considerations and development of the used code were a lot of fun. However, I never
used it myself. It doesn't suit my style of working. What I have to offer here isn't suitable for a tablet. In my world tablets are a plague anyway, but my world isn't yours. If you want to apply the principles of the solution, but to adapt it to your needs, the simplest way to replace the modifier functionality would be to omit (not use) the related code, and to add sensitive areas to your document toolbar including the modifications. You will need then an additional one-line-caller per area.
Finally: AOO doesn't support the
Alt modifyer with toolbar clicks.
Re: Create floating button to add values to cells in Calc
Posted: Mon Sep 13, 2021 4:25 pm
by M.Teich
@JohnSUN-Pensioner: can the size of the control be increased? In your previous solution, using the scrollbar, there was an easy option, but I can't find anything similar in the current macro. That said, my ability to read and understand StarBasic is rather limited...
Re: Create floating button to add values to cells in Calc
Posted: Mon Sep 13, 2021 8:00 pm
by JohnSUN-Pensioner
Oh, sorry, I completely forgot that I promised to explain step by step how to do this.
There are lines in the main procedure
onChngSelected()
Code: Select all
REM Size and position
oBtnGroup.setSize(oCell.Size)
Here
oCell.Size is the size of the current cell. Actually .Size is a property, which is a simple
com.sun.star.awt.Size structure with two fields
.Width and
.Height.
This means that you can declare an auxiliary variable like this:
Code: Select all
Dim aSize As New com.sun.star.awt.Size
And set the field values as needed. For example, twice as wide as the current cell and three times as high:
Code: Select all
aSize = oCell.Size
aSize.Width = aSize.Width * 2
aSize.Height = aSize.Height * 3
or just fixed values that are the same for any cell (don't forget that these are hundredths of a millimetre!)
Code: Select all
aSize.Width = 1000
aSize.Height = 6000
Now just give the group of controls this size:
Re: Create floating button to add values to cells in Calc
Posted: Mon Sep 13, 2021 8:10 pm
by Villeroy
Villeroy wrote:Instead of moving data, any professional solution would store data on a database server.
I just have to repeat this. Creating a database with a form would be less time consuming than following this topic, not to talk about the pain of macro programming.
Re: Create floating button to add values to cells in Calc
Posted: Mon Sep 13, 2021 8:22 pm
by JohnSUN-Pensioner
@Villeroy You probably haven't noticed what all this fuss is about. It's about fast data entry from a tablet in the field (waist-deep in water). Not about saving, but about speed change values, not in the usual version of the office, but in the "adapted" for Android ...
Unusual conditions call for unusual solutions.
By the way, Happy Programmers Day!
Re: Create floating button to add values to cells in Calc
Posted: Mon Sep 13, 2021 8:35 pm
by Villeroy
JohnSUN-Pensioner wrote:@Villeroy You probably haven't noticed what all this fuss is about. It's about fast data entry from a tablet in the field (waist-deep in water). Not about saving, but about speed change values, not in the usual version of the office, but in the "adapted" for Android ...
Unusual conditions call for unusual solutions.
By the way, Happy Programmers Day!
A tiny form specialized to enter new records with 2 controls for the part no. and the value (scrollable), OK button plus Cancel button would do the trick within 30 minutes for the years to come. Of course, a database user would not lookup the right item in order to modify its value. A database user would simply add a new value for the given item.
Re: Create floating button to add values to cells in Calc
Posted: Tue Sep 14, 2021 4:41 am
by JohnSUN-Pensioner
Yes. And besides, a timestamp will be automatically added to the recording. This will allow to have a second-by-second protocol of the process for, for example, subsequent verification with video recording in case of disputable situations.