Protecting certain cells against volatile recalculation

  • Thread starter Thread starter Schizoid Man
  • Start date Start date
S

Schizoid Man

Hi,

If I have a volatile UDF used in two different cells in a spreadsheet, is it
possible for me to 'protect' one of those cells against an F9 recalculation?

In other words, is there a way to block the volatile behavior at the
application level?

Thanks,
Schiz
 
Schiz,

You could store the value somewhere (using the calc event, every time you actually calc it), and
then where ever you want to use the value, you use the stored value instead of the UDF value. The
calc event needs to have a call to a cell where you write a key value to disallow storing the new
calc.

If Range("Blocker").Value = "Block" Then Exit Sub
Range("Storage").Value = Range("CellwithUDF").Value

HTH,
Bernie
MS Excel MVP
 
Back
Top