validate # of decimal

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?
 
E

Eduardo

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
 
G

Glenn

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?


Data validation:

=A1*100=(INT(A1*100))
 
G

Glenn

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

Regards,
Bernd

Yes!

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


=NOT(MOD(10^2*A1,1))
 
J

JoeU2004

PS....
I believe that has a limit of 1342177.27.

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 -----
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top