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

[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 !!!

 

[Ken Wright]

You can use Data / Validation with custom and an =ISNUMBER(ref) formula, but DV
is easily overridden using copy and paste.

 

[Harlan Grove]

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.

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.

 

[Harald Staff]

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