Data Validation 1

[robcarr]

Dear All,

I have been looking at the data validation command, and what i need to do is allow only 8 numbers in a cell, no less and no more and definately no letters, has anyone done this in VBA before.

Thanks

Thanks Rob.

 

[xlbo]

Rob - use the CUSTOM option on data validation and enter:
=IF(AND(ISNUMBER(A1),LEN(A1)=8),TRUE,FALSE)
where A1 is the cell you want to validate
no letters will be allowed and a length of 8 will be required HOWEVER, the user will be able to enter something like 123456.7
If you want whole numbers only then you will need:
=IF(AND(ISNUMBER(A1),LEN(A1)=8,INT(A1)=A1),TRUE,FALSE)

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[xlbo]

If you need to enter this via code, use:
With Selection.Validation
.Delete
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=IF(AND(ISNUMBER(A1),LEN(A1)=8,INT(A1)=A1),TRUE,FALSE)"
End With


Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[robcarr]

as always a good answer, works a treat thanks.

Rob.

Thanks Rob.