How can I have the fixed decinal places function for certain cells

[dleus]

I would like to avoid having to enter decimals points, but just for certian
cells, not the entire sheet.

 

[Joel]

If the cell is formated to 2 decimal places then when you type the number 2
then cell will automatically display 2.00.

Once you formated one cell for fixed decimal places you can copy just the
format and paste it to other cells. Use Paste Special (in the Edit Menu) and
select FORMAT.

 

[dleus]

Thanx Joel, but what I need is to be able to enter 1234 and have it saved in
the cell as 12.34, or enter 56 and have it saved as .56

 

[Ron Rosenfeld]

I would like to avoid having to enter decimals points, but just for certian
cells, not the entire sheet.

I believe the only way to do that is with an event-triggered macro.

Here's one way that might work -- I don't know if I've checked for all the
possibilities, so make sure it works as you expect.

To enter this, right-click on the worksheet tab and select View Code.

Paste the code below into the module that opens.

Change AOI to refer to the cell(s) where you are interested in having a fixed
decimal. As written, it refers to Column A. (I coded to divide by 100, but if
you need a different value, or different values for different cells, that logic
can be incorporated).

Let me know if this does what you want.

===============================================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim AOI As Range, c As Range
Set AOI = Range("A:A")
If Not Intersect(Target, AOI) Is Nothing Then
For Each c In Intersect(Target, AOI)
With c
If InStr(.Value, ".") = 0 And _
Len(.Value) > 0 And _
.HasFormula = False And _
IsNumeric(.Value) Then
.Value = .Value / 100
End If
End With
Next c
End If
End Sub
=================================
--ron

 

[Pete_UK]

Click on Tools | Options | Edit tab and then check Fixed Decimal (half-
way down) and set it to 2. Now when you enter a number it will be
assumed that you want 2 decimal places. Be sure to reverse the setting
once you are done (or you could have a macro tied to a button which
toggles between the two settings).

Hope this helps.

Pete

 

[dleus]

Ron
Thank You. . .

This is exactly what I wanted. I have no idea what all the code you wrote
does, but it worked great. Could you tell me how I would set another col on
the sheet, say col D, to do the same thing. (I did figure out how to change
the # decimal points, but couldn't figure out how to get another col set up)

Again, thanx for your help
doug

 

[Gord Dibben]

Make alterations to AOI

Set AOI = Range("A:A, D")


Gord Dibben MS Excel MVP

 

[Ron Rosenfeld]

Ron
Thank You. . .

This is exactly what I wanted. I have no idea what all the code you wrote
does, but it worked great. Could you tell me how I would set another col on
the sheet, say col D, to do the same thing. (I did figure out how to change
the # decimal points, but couldn't figure out how to get another col set up)

Again, thanx for your help
doug

Your welcome. Thanks for the feedback.

When you say "set another col on the sheet...", I presume you mean to "add"
that column to the column A already set, since I wrote how to change it in my
previous post.

To have multiple areas set to behave this way, you could use the Union method.
So to include columns A and D, you would:

Set AOI = Union(Range("A:A"), Range("D"))

The other code basically ensures that the entry is in your area of interest;
you are dealing with a number that does not contain a decimal point, and not a
formula or a non-numeric entry.

In doing some additional testing, it seems I overlooked some entries that will
give an incorrect result (e.g. error values and booleans), so I would change
the code to this, which will hopefully cover the various entries that might get
made.

====================================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim AOI As Range, c As Range
Set AOI = Union(Range("A:A"), Range("D"))
If Not Intersect(Target, AOI) Is Nothing Then
Application.EnableEvents = False
For Each c In Intersect(Target, AOI)
With c
If VarType(.Value) >= 2 And _
VarType(.Value) <= 6 And _
.HasFormula = False Then
If InStr(.Text, ".") = 0 Then .Value = .Value / 100
End If
End With
Next c
End If
Application.EnableEvents = True
End Sub
===========================
--ron

 

[dleus]

I apoligize for not being clear on what I'm trying to do.
What I need is for 1 col to be divided by 100 and another col to be divide
by 10. One col is a price ($) and the other col is a quantity that has 1
digit after the decimal. I'm trying to get it so that I don't have to enter
the decimal points all the time.
Your code is working great for me, I just don't know how to set it up for 2
different cols and 2 different divisors.
Thanx for your patience
doug

 

[Ron Rosenfeld]

I apoligize for not being clear on what I'm trying to do.
What I need is for 1 col to be divided by 100 and another col to be divide
by 10. One col is a price ($) and the other col is a quantity that has 1
digit after the decimal. I'm trying to get it so that I don't have to enter
the decimal points all the time.
Your code is working great for me, I just don't know how to set it up for 2
different cols and 2 different divisors.
Thanx for your patience
doug

Something like this for Columns A/100 and D/10 should give you some ideas.

===============================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim AOI As Range, c As Range
Set AOI = Range("A:A,D")
If Not Intersect(Target, AOI) Is Nothing Then
Application.EnableEvents = False
For Each c In Intersect(Target, AOI)
With c
If VarType(.Value) >= 2 And _
VarType(.Value) <= 6 And _
.HasFormula = False Then

If .Value = Int(.Value) Then
Select Case c.Column
Case Is = 1 'Column A
.Value = .Value / 100
Case Is = 4 'Column D
.Value = .Value / 10
End Select
End If

End If
End With
Next c
End If
Application.EnableEvents = True
End Sub
===============================

Also, please note that this routine does NOT work the same as the "Fixed
Decimal" selection in Excel. In particular, it will divide any integer. So
entries in Column A of

12
12.
12.00

will all be converted to 0.12

If you want to enter an integer, you must follow it by two zeros in a "divide
by 100" column, and by a single zero in a "divide by 10" column.

A1: 1200 --> 12
D1: 120 --> 12

--ron

 

[Ron Rosenfeld]

Also, please note that this routine does NOT work the same as the "Fixed
Decimal" selection in Excel.

If you prefer to have things work as per the "Fixed Decimal" option, and can
avoid having to select multiple cells for data entry in your "adjusted"
columns, you could use this event macro instead. If you are NOT in one of the
desired columns, the Fixed Decimal option is turned off.

It turns on the FixedDecimal option when you jump into a cell in the desired
ranges; and sets the number of decimals according to the column.

===============================
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim AOI As Range
Set AOI = Range("A:A,D")
Application.FixedDecimal = False
If Not Intersect(Target, AOI) Is Nothing Then
If Target.Count <> 1 Then
MsgBox ("Only select a single cell for data entry")
Exit Sub
Else
Application.FixedDecimal = True
Select Case Target.Column
Case Is = 1 'Column A
Application.FixedDecimalPlaces = 2
Case Is = 4 'Column D
Application.FixedDecimalPlaces = 1
End Select
End If
End If
End Sub
==========================
--ron