Ask Your Question

Pushbutton +1 to cell value

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

unknown1001 gravatar image

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


I need macro to add +1 value to C1 cell

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

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

Lupp gravatar image

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

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 +0100 )edit

Of course. See "Edit2"

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

Its not working its still linked only to one cell

unknown1001 gravatar imageunknown1001 ( 2018-05-22 16:15:11 +0100 )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 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


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

Seen: 450 times

Last updated: May 22 '18