# Thread: Limit number of decimals in a cell (Office 97 SR2)

1. ## Limit number of decimals in a cell (Office 97 SR2)

The problem I have is the following:

For a sheet that's being build we have to limit the number of decimals to 2. So whenever a user tries to input 3 decimals the input should be rejected. (e.g. 2.12 would be accepted but 2.129 not).

Setting the cell format to number with 2 decimals is not the solution since this would be rounded of and could cause problems in the calculations further down the road.

I had hoped validation would provide this function but unfortunately it doesn't. I guess I will have to use VBA but I'm not really good at it. Can someone help me?

Thnx in advance

Dennis

2. ## Re: Limit number of decimals in a cell (Office 97 SR2)

The code below, entered in the Workbook Sheet Change event routing in the module behind ThisWorkbook should do what you want. However, this code will not allow more than two decimal digits anywhere in the workbook. If you need to limit it to a smaller area, this will have to be modified.

<pre>Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
If IsNumeric(Target) Then
If InStr(Target, ".") > 0 Then
If (Len(Target) - InStr(Target, ".")) > 2 Then
MsgBox "You must not enter more than 2 decimal digits"
Target.Select
End If
End If
End If
End Sub
</pre>

3. ## Re: Limit number of decimals in a cell (Office 97 SR2)

You can use Validation. Use this custom formula:

=LEN(\$A\$1-INT(\$A\$1))<=4

4. ## Re: Limit number of decimals in a cell (Office 97 SR2)

How easy it seems when you look at this answer <img src=/S/grin.gif border=0 alt=grin width=15 height=15>.

You are great thanks <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23> <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•