Fixed decimal places in Excel

[Garben]

I am designing an Excel spreadsheet as a template for data entry. I would
like the cell to only allow two decimal places to be entered. For example,
if a person entered 1.234, I would like the value to be rejected. For my
purposes, this would not be best accomplished through the fixed decimal
option as the user can still enter this value and have it accepted. I have
Excel 2003.

 

[Bernie Deitrick]

Garben,

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.

HTH,
Bernie
MS excel MVP


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

 

[evoxfan]

This might help get you started.

Go to Data then validation then custom.

Then put in the Fixed( , ) formula. The first space is the number in
question which can be a cell reference and the next number is the number of
digits you want a number rounded to. This will round to your number of
digits instead of reject numbers with excess decimals. It may be beneficial
in that the one entering data will not have to round the number since it is
done for them.

This same formula can also be used in a cell without the data validation
part if that helps any.

 

[Garben]

Thanks - this is what I needed! I just need to change it to reference a
column instead of the whole worksheet.

 

[Bernie Deitrick]

Use

If Target.Column <> 1 Then Exit Sub

to control the column it works on: 1 for A, 2 for B, etc.

HTH,
Bernie
MS Excel MVP