We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

macro math operation via push button: add value +1 [closed]

asked 2020-05-24 08:36:03 +0200

cybersurfer5000 gravatar image

updated 2021-05-21 02:08:25 +0200

Alex Kemp gravatar image

Hello :D

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

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2021-05-21 02:08:40.478211


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

ajlittoz gravatar imageajlittoz ( 2020-05-24 09:39:25 +0200 )edit

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

cybersurfer5000 gravatar imagecybersurfer5000 ( 2020-05-24 10:32:48 +0200 )edit

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

ajlittoz gravatar imageajlittoz ( 2020-05-24 10:49:41 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2020-05-24 11:52:54 +0200

Hello @cybersurfer5000

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

  1. Insert -> Form Control -> Spin 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

edit flag offensive delete link more


Thank you!

cybersurfer5000 gravatar imagecybersurfer5000 ( 2020-05-24 19:36:44 +0200 )edit

answered 2020-05-24 12:05:55 +0200

Opaque gravatar image

updated 2020-05-24 12:22:16 +0200


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
edit flag offensive delete link more


It works great! Thank you very much.

cybersurfer5000 gravatar imagecybersurfer5000 ( 2020-05-24 19:42:07 +0200 )edit

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

cybersurfer5000 gravatar imagecybersurfer5000 ( 2020-05-24 19:51:33 +0200 )edit

.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

Opaque gravatar imageOpaque ( 2020-08-31 13:05:32 +0200 )edit

Question Tools

1 follower


Asked: 2020-05-24 08:36:03 +0200

Seen: 251 times

Last updated: May 24 '20