# To format a cell max four number after decimal

Hi Experts,

I have a VBA code which create and change excel cell format validation rull.
Want a cell that allow user only to enter maximum four digit after a decimel (expl. 234.2345)

for example,the following code allow the user to enter only 3 digit in the cell K:K

With .Range("K:K").Validation       ' Normal Lead Time (working days)
.Delete
Operator:=xlBetween, Formula1:="1", Formula2:="999"
.IgnoreBlank = True
.ErrorMessage = "You can only enter three digits into this field"
.ShowError = True
End With
Same as I mentioned above, want the cell P:P allow only 4 digit after a decimel if the user enter more than 4 digit after a decimal then disply message  like "You can enter only 4 digit after the decimal"

Thanks
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mechanical EngineerCommented:
One way of doing the validation is to use a Custom validation that compares the value in the cell to that value rounded to four digits after the decimal place. In other words, a formula like:
=P1=ROUND(P1,4)
``````Sub FourDecimalValidation()
Dim cel As Range
Dim frmla As String
With ActiveSheet
With .Range("P:P")
Set cel = .Cells(1, 1)
frmla = "=RC=Round(RC,4)"
frmla = Application.ConvertFormula(frmla, xlR1C1, xlA1, False, cel)
With .Validation
.Delete
.IgnoreBlank = True
.ErrorMessage = "You may enter only 4 digits after the decimal"
.ShowError = True
End With
End With
End With
End Sub
``````
0
Author Commented:
Hi
I tried as below but getting compile error."Method or data member not found"

With .Range("R:R")
Set cel = .Cells(1, 1)
frmla = "=RC=Round(RC,4)"
frmla = Application.ConvertFormula(frmla, xlR1C1, xlA1, False, cel)
With .Validation
.Delete
.IgnoreBlank = True
.ErrorMessage = "You may enter only 4 digits after the decimal"
.ShowError = True
End With
End With

Thanks
0
Mechanical EngineerCommented:
I tested the code before posting it. It works on my laptop in both Excel 2003 and 2013 when the validation is being set on the active worksheet. There was no worksheet protection, nor were there any merged cells in my test workbook.

Could you please post which version of Excel that you are using?
Could you also please post a workbook, including the macro, that demonstrates the problem?

0
Author Commented:
I am using office 2013.

if I use the following no error but it's not doing the way I want( limit max 4 digit after a decimal).
and it allowing more than 4 digit after decimal

With Range("R:R").Validation
.Delete
xlBetween, Formula1:="=ROUND(R2,4)=R2"
.IgnoreBlank = True
.ErrorMessage = "You may enter only 4 digits after the decimal"
End With

Thanks
0
Mechanical EngineerCommented:
That code will offset the validation by one cell. In other words, the validation formula in cell R2 will be =ROUND(R3,4)=R3

Try changing the With statement as shown below:
``````With Range("R2:R1048576").Validation
.Delete
xlBetween, Formula1:="=ROUND(R2,4)=R2"
.IgnoreBlank = True
.ErrorMessage = "You may enter only 4 digits after the decimal"
End With
``````
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
I tried but getting "Application defined or object defined error" message. in line
With Range("R2:R1048576").Validation
0
Mechanical EngineerCommented:
Could you please post the workbook that generates this error message?
0
Mechanical EngineerCommented:
I can reproduce your error message if the worksheet is protected. You must unprotect the worksheet before trying to create the validations.
0
Author Commented:
Thanks a lot
0
Mechanical EngineerCommented:
Was worksheet protection the reason the code didn't work?
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.