HI.
How do I start a macro when the value changes in a cell?
Example: Cell B2=“SI” which is derived from IF instructions on other cells.
If the cell becomes “no” (always with IF instruction from verification on other cells) the Sub “Myprocedure” starts.
I read for the instruction “SheetCellRange object”. How to apply it.
That would work for me. I would just need you to trigger the event every time the value changes (which can only be “SI” or “no”).
It is very simple, you don’t need to know anything about the “SheetCellRange object”.
Just write your subroutine as a function with a parameter (user-defined function, UDF). And place it in any cell on the sheet as a formula that refers to the cell whose changes you want to track.
There is only one limitation - the function should not change the contents of other cells on the same sheet. This will not work for it - for the time of recounting formulas, active sheet is blocked from changes.
Just small example - When_cell_is_modified.ods
If your procedure should manipulate the cells of the active sheet, you will have to use a slightly more complicated way - assign a macro to the sheet event
There is no event broadcasted on recalculation telling which cells changed their value.
Thus you would need to have your macro check itself for the probable changes interesting you after any recalculation.
You need to assign a Sub with 0 parameters to the sheet event ‘FormulasCalculated’.
Depending on what you want to achieve you need to consider that dirty cells of currently not active sheets will not be recalculated if not referenced from the active sheet.
I don’t know details about the conditions for raising the mentioned event.
The sheet event ContentChanged passes a parameter giving the concerned CellRange / CellRanges. It is only thrown for changes of the content itself, not for changed formula results.