[Solved] Button to activate a cell formula

[berec_halfhand]

Good day.
I am working on this table. This table has many lines with different values on them. I put a 'checkmark' box at the beginning of each line. My plan is that if you click and check the box, the line becomes 'active'; but if left unchecked, it's inactive and no value from the formula it has on it will change.
Once 'active', the formula at a specific cell will run and a value will be assigned.
The formulas in the cells are pretty simple, ie. multiplications, sums, etc.
After googling it, apparently it's asking for a macro, but I have not the slightest idea how to create one. And as a newbie on this forum I don't know where to look for or what it is that I'm looking for. Button Activation? Cell Activation? Line Activation?
I appreciate any help.

[MrProgrammer]

if left unchecked, it's inactive and no value from the formula it has on it will change.

This isn't how spreadsheets normally work. Spreadsheets evaluate a formula when any cell used in the formula changes. You might be able to achieve this by protecting cells. Investigate Tools → Protect Dcument → Sheet and then Format → Cells → Cell Protection. You may be able to record macros which protect and unprotect a row of cells. Using a checkbox to protect and unprotect cells will require you to write a macro using the UNO API and will increase the difficulty by a factor of 100. Read about cell protection in Help → Index or in User Guides (PDF) or searching for topics about this in the Calc Forum.

[Tutorial] Ten concepts that every Calc user should know
[Tutorial] Favorite Recorded Calc Macros

[Lupp]

I don't think there is a way to protect cells against recalculations, and "AutoCalculate Off" is global for the document.

Showing values which were calculated in the past the used cells must not contain formulas.
You may, however, have formulas in a second sheet (possibly hidden) where calculations following the known rules are performed, but by default not passed to the "showing" sheet.

From that helper sheet you can pull the current results (and direct content) for defined ranges (rows in your case) into the showcase on demand.

Code to do so using the API isn't exactly complicated.

However, the ways of handling "dirty cells" and their dependents are at the very heart of spreadsheet technology. You have to consider that there may be unexpected side effects depending possibly on the way how you want to trigger the pulling.

To create a FormControl per row requires too much handywork, but you may define one such control, and work with the row which contains the cell having the focus.

Ways to trigger the pulling are
=1= the usage of a sheet event for "Content changed".

aooStrangeOnContentChanged.ods

(19.1 KiB) Downloaded 4 times

=2= "OK" click on a single button inserted into the showing sheet.

aooStrangeWithOkButton.ods

(24.64 KiB) Downloaded 5 times

=3= the usage of an additional item of the cell context menu.

aooStrangeWithCellContextItem.ods

(24.53 KiB) Downloaded 8 times

=4= The usage of an extra toolbar is not demonstrated.

To do it with a recorded macro (without additional code) seems to be not possible.
Personally I would (in principle) prefer the third solution, but
== The customization of context menus, available in LibreOffice, is not supported by AOO.
== The additional item is also shown for sheets to which nothing shall be pulled.

[JeJe]

A slightly different approach would be copying the formulas to a hidden location on unticking and moving them back when ticking the row as active. Again a macro would be needed, but it wouldn't have to be one connected to sheet events.

[Lupp]

Only one of the three exemplified suggestions uses a sheet event, and this isn't the preferred one.
The solution using an OK button is my favorite, because it neither afflicts other sheets nor is afflicted by possible unconscious triggering.

[Lupp]

@OQ:

Sending emails without a really (justified) personal content isn't how forums work.

Clarifications and the like should be posted the regular way.
In many cases a possibility of having an https://en.wikipedia.org/wiki/XY_problem,should be considered.

(Sorry. The linked article is currently under discussion.)

[berec_halfhand]

You're right. I apologize. I wasn't aware. let me erase my question until further notice.