Create floating button to add values to cells in Calc

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

[JeJe]

Which cell is selected is already indicated. Why not just have two buttons in a toolbar?

[Villeroy]

This is trivial to do with a database and a form.

[M.Teich]

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.

[M.Teich]

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.

[Villeroy]

Instead of moving data, any professional solution would store data on a database server.

[M.Teich]

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.

[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?

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

[M.Teich]

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?

[JeJe]

Can the equivalent solution in StarBasic be that much more complicated?

Afraid so...

[Bidouille]

Without macro and with spin button controls

[JeJe]

The attached moves a spin button location to the selected cell.

[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.)

[JohnSUN-Pensioner]

Ok, try this option

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

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

[JeJe]

Code: Select all

thiscomponent.currentcontroller.componentwindow.invalidate(0)

Seems to work at the end of the ShowOrHideGroup sub.

[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

Code: Select all

ShowOrHideGroup(oBtnGroup, True) 

in onChngSelected sub (no need call .invalidate() for full window each time)

Good point. @M.Teich, please consider this change in your project

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

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

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

[eeigor]

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

[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:

ask233471_IncrementDecrementByModifiedToolbar20210909.ods

(31.6 KiB) Downloaded 312 times

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.

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

[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:

Code: Select all

oBtnGroup.setSize(aSize)

[Villeroy]

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.

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

[Villeroy]

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

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