W
Werner Rohrmoser
Hello,
I've made an UDF to determine, whether a cell has a formula or not,
see listing below:
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Public Function CellHasFormula(Cell As Range) As Variant
' Error trapping
On Error GoTo FuncFail:
' Exit, if cell has formula and cell isn't recalculated.
If IsEmpty(Cell) And Len(Cell.Formula) > 0 Then Exit Function
' determines whether cell has formula
CellHasFormula = Cell.HasFormula
Debug.Print Cell.Row & " / " & Cell.Column
Exit Function
' Error trapping
FuncFail:
CellHasFormula = CVErr(xlErrNA)
End Function
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
The tested cell is referenced by the argument of the UDF, so it should
calculate whenever the referenced cell is changed and in general it
works as it should.
But now, the part I don't understand.
I have a cell which is no precedent to the cell with the udf and I can
change it in two ways:
1. I select a value from the validation dropdown
2. I enter a valid value manually into the cell.
When I do it the first way, the UDF is calculated and when I do it the
second way it is not calculated.
Is it a difference to use validation drop down or not?
Regards
Werner
Excel XP SP 3
WIN XP SP 3
I've made an UDF to determine, whether a cell has a formula or not,
see listing below:
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Public Function CellHasFormula(Cell As Range) As Variant
' Error trapping
On Error GoTo FuncFail:
' Exit, if cell has formula and cell isn't recalculated.
If IsEmpty(Cell) And Len(Cell.Formula) > 0 Then Exit Function
' determines whether cell has formula
CellHasFormula = Cell.HasFormula
Debug.Print Cell.Row & " / " & Cell.Column
Exit Function
' Error trapping
FuncFail:
CellHasFormula = CVErr(xlErrNA)
End Function
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
The tested cell is referenced by the argument of the UDF, so it should
calculate whenever the referenced cell is changed and in general it
works as it should.
But now, the part I don't understand.
I have a cell which is no precedent to the cell with the udf and I can
change it in two ways:
1. I select a value from the validation dropdown
2. I enter a valid value manually into the cell.
When I do it the first way, the UDF is calculated and when I do it the
second way it is not calculated.
Is it a difference to use validation drop down or not?
Regards
Werner
Excel XP SP 3
WIN XP SP 3