validate # of decimal

  • Thread starter Thread starter MauraC
  • Start date Start date
M

MauraC

I would like to validate cells to limit input to only 2 decimals. If the
user inputs a value with > 2 decimals, I want to system to return an error
message.

Is there a way to do this without the use of VB?
 
Hi try this solution given some time ago in the community

Copy the code below, right-click the sheet tab, select "View Code" and paste
the code into the window that appears.
I have assumed that you want to do checking only on single cell entries.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
If Target.Value <> Round(Target.Value, 2) Then
Target.Value = ""
MsgBox "Bad value. Bad, bad, value. Rejected!!!!"
End If
Application.EnableEvents = True
End Sub
 
MauraC said:
I would like to validate cells to limit input to only 2 decimals. If the
user inputs a value with > 2 decimals, I want to system to return an error
message.

Is there a way to do this without the use of VB?
Click to expand...


Data validation:

=A1*100=(INT(A1*100))
 
Bernd said:
Data validation:
=NOT(MOD(100*A1,1))

Regards,
Bernd
Click to expand...

Yes!

Although as an afterthought, I would use this, which makes the number of decimal
places more obvious:


=NOT(MOD(10^2*A1,1))
 
PS....
I believe that has a limit of 1342177.27.
Click to expand...

Perhaps I need to say "in Excel 2003". I don't know about Excel 2007. Then
again, the OP did not identify the Excel version she is working with.


----- original message -----
 
Back
Top