validation for decimals

  • Thread starter Thread starter Avian Rand
  • Start date Start date
A

Avian Rand

Hi there.

I need to set a series of cells so that the user cannot enter anything
other than 2 decimal currency values. Here's my code for this:

..Range("K" & sI & ":K" & sI).Validation.Add Type:=xlValidateDecimal,
AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="0.00",
Formula2:="999999999.99"

It works but it still allows the user to enter more than 2 decimal
places. There is no setting that I can find to limit the user so
he/she cannot enter 3 or 4 or 5 or more decimal places.

I know I can foramt the cells to only show 2 decimals but that's not
really sufficient. It doesn't prevent entry for more than 2 decimals.
And even though it's formatted to only show 2, if the user enters 3 or
more then those values are actually still in the cell even though they
don't show up.

I'm reading the data from the returned spreadsheet into a databasse,
so I need only 2 decimlas. And of course I know I can round when I
extract the data from teh spreadsheet but again that does not prevent
the user who's filling in the spreadsheet from entering more than 2
decimials.

So in short, how do I prevent the user from entering more than 2
decimals?

Thanks.
 
Just to be clear, I also need to include the validation that I'm
already doing. That is that the value must be a number between 0.00
and 999,999,999.99 (inclusive)
 
I found this:

=OR(IF(ISERROR(
FIND(".",$A1)),LEN($A1)>0,LEN(MID($A1,FIND(".",$A1)+1,25))<3))

It does restrict to 2 decimal places (and carefully only allows a
single decimal point) but it allows negatives and doesn't limit the
values max amount.

I don't know how to modify it to do that. Can anyone help?

Thnaks.
 
Hi Avian

I think this does it
=AND(A1>=0,A1<1000000,OR(IF(ISERROR(FIND(".",$A1)),
LEN($A1)>0,LEN(MID($A1,FIND(".",$A1)+1,25))<3)))
 
Back
Top