restriction on a number field

  • Thread starter Thread starter Grace
  • Start date Start date
G

Grace

Can I set a number field to only allow numbers entered in
tenths. For example, I do not want a user to be able to
enter 1.75 and have it round up to 1.8. I need to
restrict the entry to only 1 decimal point.
 
You could restrict the entry by using Data|Validation
Custom
and a formula like:
=((A1*10)=INT(A1*10))

You could also have an event macro just do the rounding without asking.

Rightclick on the worksheet tab that should have this behavior. Select view
code. Paste this into the code window:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a1")) Is Nothing Then Exit Sub

On Error GoTo errHandler:
With Target
If IsNumeric(.Value) Then
Application.EnableEvents = False
.Value = Application.RoundUp(.Value, 1)
End If
End With

errHandler:
Application.EnableEvents = True

End Sub


Adjust A1 to the range you want to check.
 
Back
Top