Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SheetChange for formulas
I've noticed that if a change the value of a cell that affects a formula for
that cell the Worksheet.Change event is triggered but for the cell with the formula is not. Is there a way to determine if the value of a formula has changed? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SheetChange for formulas
Normally a formula cell will contain a refer to one or more precedents, of
which at least one is likely to be a cell you can look for changes in a change event. Alternatively, start by storing the old formula value, then comparing at the end of a Calculate event, eg Make B4 a formula cell, eg =Sum("B2:B3") in the worksheet module (rt-click sheet tab, view code) Dim mbStored As Boolean Dim mV As Variant Private Sub Worksheet_Calculate() Debug.Print Range("B4").Value With Range("B4") If Not mbStored Then mV = "last value stored" End If Range("C4").Value = mV mV = .Value End With mbStored = True End Sub Initialize (ie store the old value) by causing a recalc, or manually Ctrl-Alt-F9 Look at cell C4 Regards, Peter T "ARHangel" wrote in message ... I've noticed that if a change the value of a cell that affects a formula for that cell the Worksheet.Change event is triggered but for the cell with the formula is not. Is there a way to determine if the value of a formula has changed? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SheetChange for formulas
Is there a way without knowing the cells with formulas?
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
SheetChange for formulas
In theory maybe yes but difficult to comment without any details as to what
you have and what you want. At the very least it would need to be tailored to your set-up and requirements. Regards, Peter T "ARHangel" wrote in message ... Is there a way without knowing the cells with formulas? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
SheetChange for formulas
If anyone interested I've found a solution.
use Range.Dependents - gives you all the cells that are formulas and the formulas "contains" the Range or Range.SpecialCells(xlCellFormulas) - gives you all the cells in the Range that are formulas "Peter T" wrote: In theory maybe yes but difficult to comment without any details as to what you have and what you want. At the very least it would need to be tailored to your set-up and requirements. Regards, Peter T "ARHangel" wrote in message ... Is there a way without knowing the cells with formulas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cause of SheetChange Event | Excel Programming | |||
SheetChange fire UDF unintentionally | Excel Programming | |||
SheetChange Event | Excel Programming | |||
SheetChange Event | Excel Programming | |||
XLapp and SheetChange | Excel Programming |