Ask Your Question

Create button for simple addition

asked 2016-09-06 10:07:04 +0200

Alexandru gravatar image


I need to create a simple button which increases the number by 1 in a specific cell each time I press it.

I've managed to create the button and I know that I must attach a macro to it. I know how to create a macro and attach it to the button. What I don't know is what should I write inside that macro.

Could someone please show me exactly how this macro should look? In such way that I can simply copy and paste it. The cell for which I want its value increased by 1 is cell F10.

Thank you.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2016-09-06 11:09:20 +0200

JohnSUN gravatar image

updated 2016-09-06 11:11:15 +0200

Code is realy very simple:

Sub onBtnClock(oEvent As Variant)
Dim sCellAddress As String 
Dim oSheet As Variant
Dim oCell As Variant
    sCellAddress = Trim(oEvent.Source.Model.Tag)    ' Get tagret cell '
REM address from additional information of button (see picture)
REM Get selected sheet (where located buttton wich is pressed)
    oSheet = ThisComponent.getCurrentSelection().getSpreadsheet()
REM Get cell by address (address may be wrong!)
    On Error Resume Next
    oCell = oSheet.getCellRangeByName(sCellAddress)
REM Is address was valid?
    If Not IsNull(oCell) And Not IsEmpty(oCell) Then
REM Get old value, increase it and set result to cell
        oCell.setValue(oCell.getValue() + 1.0)
End Sub

Of course you can write address of cell directly to parameter of metod .getCellRangeByName(). But will be better if you write it to additional information of button SetCellAddressForEachButton

edit flag offensive delete link more


Perfect, thank you very very much!

Alexandru gravatar imageAlexandru ( 2016-09-06 12:01:54 +0200 )edit

Interesting that "Additional information" can be accessed from the macro as oEvent.Source.Model.Tag. I have used similar method in Excel but was not aware that LO had similar feature due to the naming "Additional information" instead of "Tag".

mark_t gravatar imagemark_t ( 2016-09-06 15:24:11 +0200 )edit

Hi @Alexandru

No need for a macro with a Spin button. This type of button also decrement what you do may not want?


pierre-yves samyn gravatar imagepierre-yves samyn ( 2016-09-06 15:53:13 +0200 )edit

@pierre-yves samyn It seems to me becouse the spin button is too small. In a very large button is much easier to hit the mouse. And the spin button can not only increase but also decrease the value. Imagine the counter form of two dozen buttons. For some events human operator presses a certain button, fixed time, increases the value of a parameter, some process is changed. Spin button is ill-suited to such a form.

JohnSUN gravatar imageJohnSUN ( 2016-09-10 13:42:54 +0200 )edit

Hi @JohnSUN

I share of course your point of view but I wondered if Alexanru knew this control which in some cases, fits the bill.


pierre-yves samyn gravatar imagepierre-yves samyn ( 2016-09-10 14:01:26 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-09-06 10:07:04 +0200

Seen: 610 times

Last updated: Sep 06 '16