Validation seems to have a big loophole

  • Thread starter Thread starter Art Decco
  • Start date Start date
A

Art Decco

I have a validation rule set up for a cell that says the cell must contain a
"whole number" that is >= 1. When entering data, it works fine. However, it
turns out that the user is able to hit the "delete" key, effectively
entering zero into that cell.

Is there some way to tell the validator that it has to be what I said it has
to be and "<nothing>" isn't a whole number >=1? I tried checking and
unchecking the "Ignore blanks?" checkbox, but that didn't change anything.

Any suggestions?

Thanks.
 
Art,

The delete key doesn't put a 0 in the cell -- it makes it empty, and even if
it did put a 0, Validation probably wouldn't catch it. Validation also
doesn't catch pasting stuff into the cell, or setting the cell via a macro.

One way would be to have an event macro check the cell any time it's been
changed. If you're willing, we'll write you one.
 
You could use code similar to this in
the Workbook Before_Close event:

If thecell <1 Then
msgbox "Please make an entry in thecell."
Else
ThisWorkbook.Save
ThisWorkbook.Close
Note: change "thecell" to your cell reference.
 
Earl Kiosterud said:
Art,

The delete key doesn't put a 0 in the cell -- it makes it empty,
Click to expand...

Right, which is why I wrote "effectively entering zero". By making it empty,
it makes it a zero for any formula that uses it as a numeric value, even if
the validation explicitly prohibits zeros in that cell.
...and even if
it did put a 0, Validation probably wouldn't catch it.
Click to expand...

Yes, that's what I'm sort of complaining about. Validation isn't limiting
the values in the cell to what I'm telling it to limit them to.
Validation also
doesn't catch pasting stuff into the cell, or setting the cell via a
Click to expand...
macro.

Thanks for the tip. The latter isn't likely to be a problem, but the former
may be.
One way would be to have an event macro check the cell any time it's been
changed. If you're willing, we'll write you one.
Click to expand...

I'm not quite sure what you mean by, if *I'm* willing, *you'll* do the work.
Of course I'm willing to let you work for me for free. (You're not selling
contract services are you?) In fact, I'm willing to let all kinds of people
do all kinds of free work for me, so have at it. Don't be shy. ;-)

And thanks.
 
Art Decco said:
Right, which is why I wrote "effectively entering zero". By making it empty,
it makes it a zero for any formula that uses it as a numeric value, even if
the validation explicitly prohibits zeros in that cell.
Click to expand...

Another approach you could consider is to construct such formulas
differently. In other words, rather than trying to force an entry with
validation (that has its limitations), you could make the formulas respond
differently until there is a 'valid' entry in the cell. The most obvious
example would be to put the requirement for it (say, A1) to be >=1 in the
formula like this:
=IF(A1>=1,YourFormula,"Please enter data in A1")
 
Back
Top