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)

Please advice the VBA code do that.

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
        .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
        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
alam747Asked:
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.

byundtMechanical 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
            .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=frmla
            .IgnoreBlank = True
            .ErrorMessage = "You may enter only 4 digits after the decimal"
            .ShowError = True
        End With
    End With
End With
End Sub

Open in new window

0
alam747Author 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
            .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=frmla
            .IgnoreBlank = True
            .ErrorMessage = "You may enter only 4 digits after the decimal"
            .ShowError = True
        End With
    End With

Please advise what's need be added any reference or anything, waiting for your response.

Thanks
0
byundtMechanical 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?

Brad
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

alam747Author 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
        .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=ROUND(R2,4)=R2"
        .IgnoreBlank = True
        .ErrorMessage = "You may enter only 4 digits after the decimal"
End With

Thanks
0
byundtMechanical 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
        .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=ROUND(R2,4)=R2"
        .IgnoreBlank = True
        .ErrorMessage = "You may enter only 4 digits after the decimal"
End With

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
alam747Author Commented:
I tried but getting "Application defined or object defined error" message. in line
With Range("R2:R1048576").Validation
0
byundtMechanical EngineerCommented:
Could you please post the workbook that generates this error message?
0
byundtMechanical EngineerCommented:
I can reproduce your error message if the worksheet is protected. You must unprotect the worksheet before trying to create the validations.
0
alam747Author Commented:
Thanks a lot
0
byundtMechanical 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.