Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Location
    Rijswijk, Netherlands
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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>

    Legare Coleman

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    New Lounger
    Join Date
    Jan 2001
    Location
    Rijswijk, Netherlands
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •