Truncating entered text - Excel2K

[BigTeeJay]

Hey,
There may be an easy fix for this, but I cant
seem to find it for the life of me...

I need to create some sort of custom formatting
that will truncate entered numbers to two decimal
places. This way, if a user enters "48.51632",
the text will be rounded to "48.52" (not just
displayed/formatted as .52, the actual value of
the cell will only allow/round to .52).

Any ideas?

 

[bryanbayfield]

Say if you wanted this to be automatic formatting for a particular sheet then you could use this code, inserted into the relevant sheet object:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim whole As String, n As Integer
Dim dec As Integer
dec = 0
If IsNumeric(Target.Value) Then
whole = Target.Value
For n = 1 To Len(whole)
If Mid(whole, n, 1) = "." Then
dec = n
Exit For
End If
Next n
If Len(whole) - dec > 3 Then
Target.Value = Left(whole, n + 2)
End If
Target.NumberFormat = "0.00"
End If
End Sub