Thanks for the macro, Ron. I'm clearly naive about Excel -- I figured there *must*
be a simple way to get the general formatting with comma separators!
The drawback of the macro, for my purposes, is that Excel insists on widening the
columns to show all the decimal places specified in the format code given in the
macro. I would like it to behave like general formatting does by just showing the
number of decimal places that will fit, given the width of the cell.
For instance, I would like =1000*pi() to display 3,141.593 and =10000*pi() to
display 31,415.93
That specification is a bit different from your original specification which
was:
For instance, I would like
=1000*pi()
to display as
3,141.59265 (showing as many decimal places as will fit in the cell).
Do you want to leave the width of the cell fixed, at whatever you happen to set
it prior to setting the format?
If so, what should happen if the cell width is too small to display the number,
which could happen if the number is a large integer.
What do you want to happen if the cell width is not enough to display ANY of
the fractional portion?
In any event, this modification will adjust the formatting of fractional
numbers so as to fit in the current column width. If your number is
fractional, but the column width is too narrow to display ANY of the fractional
portion, it will be displayed with a terminal decimal point.
In other words, =10000*PI() might display as 31,416. if there is not enough
room to display ANY of the decimals.
This is very preliminary, and will work only for your NORMAL font, but can be
modified once you supply more precise specifications.
=================================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim AOI As Range
Dim Fmls As Range, Cnst As Range, Combined As Range
Dim c As Range
Dim W
Set AOI = Range("A:A") 'set to range(s) you may wish to format this way
'restrict AOI to just those cells containing numbers _
or formulas that --> a Number
'HELP says one can add the Type constants, but I _
could not get that to work.
On Error GoTo Handler
Set Fmls = AOI.SpecialCells(xlCellTypeFormulas, 1)
Set Cnst = AOI.SpecialCells(xlCellTypeConstants, 1)
If Fmls Is Nothing And Cnst Is Nothing Then Exit Sub
If Fmls Is Nothing Then Set Combined = Cnst
If Cnst Is Nothing Then Set Combined = Fmls
If Combined Is Nothing Then Set Combined = Union(Fmls, Cnst)
For Each c In Combined
With c
W = .ColumnWidth
If Int(.Value) = .Value Then
.NumberFormat = "#,##0"
Else
.NumberFormat = "#,##0." & Application.WorksheetFunction.Rept _
("#", Application.WorksheetFunction.Max(0, W - Len(Format _
(Int(.Value), "#,##0."))))
End If
End With
Next c
Exit Sub
Handler:
If Error = "No cells were found." Then
Resume Next
Else: MsgBox ("Error: " & Error)
End If
End Sub
==============================
--ron
