Is there a way to discern in code (say in the worksheet
change event procedure)whether or not data validation
criteria for a particular cell was met or not met in the
most recent change to the cell? I would like to be able to
set calculation to manual and then recalculate the
worksheet only if the most recent entry was valid, else do
nothing.
I have used custom functions and VBA code to validate
input, but when you condition the display of dependent
cells on those tests, it causes problems with other tools
such as Goal Seek or Solver, for example in the case where
an input cell has to be a whole number.
More generally is there a different or better approach to
stopping the cascading effect of invalid entries that
occurs when calculation is set to automatic--that is, so
the effects of erroneous data do not ripple through the
worksheet before the user responds to Retry or Cancel the
entry? I would like to set things up so the Undo history
is maintained and Goal Seek and Solver tools work as well.
Any thoughts on the best approach to follow when you have
a finite set of input data that you want to test for
validity, and you only want the results cells to change if
the new data is valid.
Thanks,
Fred Lambelet
