Want to limit input in a cell to numerical values (no alpha allowed)

  • Thread starter Thread starter Mike F.
  • Start date Start date
M

Mike F.

Is there a way to limit input in a cell to numerical
values only. I would like to have some way of rejecting
any alpha input into that cell.
Thanks in advance for you help !!!
 
You can use Data / Validation with custom and an =ISNUMBER(ref) formula, but DV
is easily overridden using copy and paste.
 
Mike F. said:
Is there a way to limit input in a cell to numerical
values only. I would like to have some way of rejecting
any alpha input into that cell.
Click to expand...

You could use a variation on the old fahioned approach - divide all
downstream calculations using this entry by ISNUMBER(ThisEntry), which will
make all downstream calculations evaluate to #DIV/0! or other error values
if ThisEntry isn't a number.

You could also use a Change event handler, but it can be defeated by
disabling events or macros generally.

As for data validation, in addition to pasting text into such cells not
triggering validation, trickiness can also defeat it. Create the defined
name foo referring to =0. Enter the formula =foo in a cell that accepts only
number. Then change the definition of foo to ="bar".

There is no secure, completely idiot-proof way to enforce data entries of
particular types. The soundest way to deal with invalid entries is to give
users more garbage out whenever they enter garbage in.
 
give users more garbage out whenever they enter garbage in.

I love that :-)
There's a distinct difference between cells and ocjects such as activex
textboxes: When entering into cells then excel goes to "entry mode"; no code
runs and no events fire. So your validation will at earliest be done when
entry is done and cell exited.

So if you have to ensure numeric entries, go for a userform with a textbox
on it. With a textbox you can validate and discard keyboard keypresses
realtime, and doing that also avoid annoying modal messageboxes telling
users how wrong they are after completed entries.

HTH. Best wishes Harald
 
Back
Top