Macro math operation via push button: add value +1

Hello :smiley:

I am struggling to write a macro to add / subtract the value of 1 to / from cell B2 when the push button is released.
image description

That is what I have so far for the macro code for the “+” button. Does somebody have an idea how to make it work?
image description

Thank you in advance!

This is not a math question (formula editor) but a calc one (spreadsheet). Please retag your question with the link above.

I can edit the tags by using the “retag” button, however I cannot save changes. Please advice.

You need to hit the Return key twice. I’ve done it for you.

Hello @cybersurfer5000

Probably you should not bother with writing macro and use Spin Button for such a task:

  1. InsertForm ControlSpin Button

  2. Draw Spin Button where necessary

  3. Right click on Spin Button and select Control option

  4. Under Data tab write desired cell address into Linked cell... field (B2 in your case)

  5. Under General tab adjust Value min, Value max, Default value, Increment/decrement value if needed.

  6. Toggle Design mode off and test created button

Thank you!


though I fully support @SM_Riga’s solution, here’s a code snippet (no error handling, no cell reference as parameter, just the idea how it could be done) which does what you want:

Sub CellValueIncrement()

    Dim oSheet As Object
    Dim oRange As Object
    Dim oCell As Object
    Dim iCurVal As Integer
    Dim iNewVal As Integer
    oSheet = ThisComponent.CurrentController.ActiveSheet
    oRange = oSheet.getCellRangebyName( "B2" )
    oCell = oRange.getCellByPosition(0,0)
    iCurVal = oCell.value
    iNewVal = iCurVal + 1 
    oCell.Value = iNewVal

End Sub

Tested using LibreOffice:

Version:, Build ID: 3d775be2011f3886db32dfd395a6a6d1ca2630ff
CPU threads: 8; OS: Linux 4.12; UI render: default; VCL: kf5; 
Locale: en-US (en_US.UTF-8); UI-Language: en-US, Calc: threaded

It works great! Thank you very much.

@anon73440385, is there a comprehensive overview of all the syntax that’s available for LibreOffice? I found the OpenOffice Basic programmer’s guide that contains some, but not all of the syntax, i.e. oSheet syntax is not described. LibreOffice’s help website link text will probable explain how to all the syntax but I have a hard time finding what I am looking for, unless I already know the syntax, but not how to correctly use it. E.g. I didnt know the oSheet syntax, hence I would not have searched for it. I hope I explained myself well.

.g. I didnt know the oSheet syntax

There is no oSheet syntax. It is the name defined within the macro (see dim statement) for the object and set to ThisComponent.CurrentController.ActiveSheet. Hence you need to look for methods and properties of ...ActiveSheet while oSheet is just a shortage for ThisComponent.CurrentController.ActiveSheet