Search code examples
buttonlibreoffice-calcopenoffice-calc

Create floating button to add/subtract values to/from cells in spread sheet

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 shows exactly what I'm looking for, i.e. a single set of floating buttons that will move relative to the selected cell.

GIF courtesy of the above mentioned answer, showing the floating red/green buttons toward the end of the animation.

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:

 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.

Solution

  • I still ask you to try the scrollbar again. Just please change your attitude to it - take it not as a control, in which you need to move the slider to the right and left, but as a set of four buttons. Don't move, just tap

    Four buttons in one control.gif

    In my example, the properties of the control "Large change" set to 10, this is the default. You can change this to 5.

    My macro has Const MAX_STEP = 20. You can change this to 10.

    And you get buttons to only having +/- 1, +/- 5 and +/- 10

    If the size of the control is not large enough to get into the necessary parts with your finger, then change the oSBar.setSize() in the code to your liking. Or just increase the zoom of the leaf, as I did when I was recording the GIF.