Formatting cell to thousands

Posted by Shirlene on January 29, 2002 4:34 AM

A user wants to type in 10 in the field and have the cell format display 10000. She does not want to type in the 000 for each entry in the column. I have tried every custom combination I can think of. Anyone have any other ideas?

Posted by JAF on January 29, 2002 5:01 AM

As far as I know, you can't change the formatting of a cell to show the value entered multiplied by 1000.

However, you can create a WorksheetChange macro to do this to change the actual value in the cell as soon as it is input:

NB: The following code should be placed in the Sheet object, not in a Module.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column = 1 Then 'Column A - Keep in to specify certain column(s)
If Not IsNumeric(Target) Then
Exit Sub
Else
Application.EnableEvents = False
Target.Value = Target.Value * 1000
Application.EnableEvents = True
End If
End If 'Keep in to specify certain column(s)
End Sub

Hope this helps
JAF

Posted by Mark W. on January 29, 2002 6:58 AM

Shirlene, this really isn't a formatting question.
It's an editing question. All you have to do is
configure your editing environment so that it places
the decimal to represent 1000's. This is accomplished
by choosing the Tools | Options... menu command.
Then on the Edit tab check the "Fixed decimal"
box, enter -3 in the "Places" field and press [ OK ].

Posted by Don C on January 29, 2002 8:44 AM

You can easily multiply the values entered by 1000 either of two ways:

In another column enter the formula =1000*cell
where cell is the cell to be increased.

or in a blank cell enter the number 1000 and copy it, then highlight the column of numbers and
/edit/paste special/multiply

Any number that needs to end up less than 1000 will need to be entered as a decimal (when entering the number 900, enter .9)