We're currently migrating from Ask to Discourse, read the details here

Ask Your Question

Pushbutton +1 to cell value [closed]

asked 2018-05-20 13:15:45 +0200

unknown1001 gravatar image

updated 2018-05-20 18:37:39 +0200


I need macro to add +1 value to C1 cell

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-10 21:06:36.736303

1 Answer

Sort by » oldest newest most voted

answered 2018-05-20 19:36:00 +0200

Lupp gravatar image

updated 2018-05-22 17:07:00 +0200

Use a SpinButton and you dont need a "macro".
A very raw version of a solution based on a Sub is also included with this demo.

For a more elaborate way to use the 'Tag' for passing parameters to a Sub assigned to an event of a FormControl see this thread. It is capable of passing more than one parameter in one string. I used the (implicit) syntax applied in the event property
'pEvent.Source.AccessibleContext.AccessibleParent.ExtendedAttributes' e.g.

New demo concerning the comments below.

The button is not linked to any cells as FormButtons can be if their object editor has a Data tab with a property Linked cell.
The kind of "fake" linkage implemented by my code is based on the .Tag property of the respective Model. Its name in the editor is Additional information for unknown reasons, probably because the designer thought it was funnier to have the user guess the correlation between different names.
The Tag of the button in the 'New demo' is now evaluated for a semicolon-separated list of cell addresses.
The example has "Sheet1.C1;Sheet1.B15" there. You may add to the list regarding the obvious syntax.

The included code is raw and doesn't test for any possible errors. It works flawlessly, however, if there aren't such errors. Check the very simple code yourself:

Sub incBy1(pEvent)
g = Split(pEvent.Source.Model.Tag, ";")
For j = 0 To Ubound(g)
    h = Split(g(j), ".")
    theCell = ThisComponent.Sheets.GetByName(h(0)).GetCellRangeByName(h(1))
    theCell.Value = theCell.Value + 1
Next j
End Sub

(It also works with cells in different sheets.)

edit flag offensive delete link more


It is possible to add +1 to two cells same time?

unknown1001 gravatar imageunknown1001 ( 2018-05-21 20:08:58 +0200 )edit

Of course. See "Edit2"

Lupp gravatar imageLupp ( 2018-05-21 20:15:46 +0200 )edit

Its not working its still linked only to one cell

unknown1001 gravatar imageunknown1001 ( 2018-05-22 16:15:11 +0200 )edit

Just downloaded the 'New demo' from the second edit to my answer above and tried to verify your claim. It falsified.
If will add and´'Edit3 shortly.

Lupp gravatar imageLupp ( 2018-05-22 16:51:46 +0200 )edit

Question Tools

1 follower


Asked: 2018-05-20 13:15:45 +0200

Seen: 593 times

Last updated: May 22 '18