How to use the Worksheet Change event to monitor if cells have errors?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Fayyaadh@gmail.com - 26 Apr 2008 18:29 GMT

Hi there

I have a sheet where some cells have (quite long) formulas in them.
Sometimes, if the cells that they refer to are empty, the formula
cells will have #VALUE! and #N/A errors in them. I could use something
like =IF(ISERROR(formula),"",formula) to show a blank cell if there's
an error but the problem is that some of the formulas are very long
and using this method will just make them much longer.

Is there any way that I can use the WorsheetChange event (or anything
else in VBA event-related) to constantly check cells to see if they
have errors or not and if they do, to just make the cell blank.

Reply to this Message

Dave Peterson - 26 Apr 2008 18:40 GMT

You may be able to use the worksheet_calculate event (since you're using
formulas). But as soon as you replace the formula that returns the error with a
"" (or clearing the contents), then the formula is gone.

Personally, I think your solution to use:
=if(iserror(yourformula),"",yourformula)
is what I'd use.

But an alternative that won't replace the error and won't change the formula is
to use format|conditional formatting (xl2003 menus).

Select the range to "fix" (say A1:B9):
with A1 the activecell
Format|conditional formatting
Formula is:
=iserror(a1)
and give it a format that hides the error (white font on white fill???).

The cell still holds the error -- so you'll have to watch out in other
calculations, but it'll look pretty.

> Hi there
>
[quoted text clipped - 8 lines]
> else in VBA event-related) to constantly check cells to see if they
> have errors or not and if they do, to just make the cell blank.

Signature

Dave Peterson

Reply to this Message

Fayyaadh Ebrahim - 26 Apr 2008 18:50 GMT

> You may be able to use the worksheet_calculate event (since you're using
> formulas). But as soon as you replace the formula that returns the error with a
[quoted text clipped - 35 lines]
>
> Dave Peterson

Hmmm, that actually works pretty well, thank you!

Reply to this Message