format problem

[bz]

format problem:

user needs to be able to enter numbers into a column and have trailing
zeros (if present) retained. [0.10, 0.100, and 0.1000 are different!]

..numberformat='@' works for this feature (text mode)

user also needs to be able to enter formula into cells in this column and
have them execute.

text format does NOT work for this feature, excel considers what is entered
as text and does NOT execute the formula.

..numberformat='general' will allow execution, but loses trailing zeros

How can I both retain trailing zeros and enter and execute formula into the
cells?

I guess I could
1) force format to text when a cell is selected
2) on change
check to see how many digits to the right of any decimal place
and create a format #.00... that has the same number of digits and
apply that to the cell.
if a formula is present[an = sign in first space], put it back and
hope that it will now be evaluated.

Is there an easier way to do this?


--
bz

please pardon my infinite ignorance, the set-of-things-I-do-not-know is an
infinite set.

(e-mail address removed)

 

[Don]

=VALUE(A1)+VALUE(A2)


Don

 

[Tom Ogilvy]

Is there an easier way to do this?

you could format as text and then have your formulas convert the number -
hard to tell what you mean by execute formula into the cells.

 

[bz]

format problem:

user needs to be able to enter numbers into a column and have trailing
zeros (if present) retained. [0.10, 0.100, and 0.1000 are different!]

.numberformat='@' works for this feature (text mode)

user also needs to be able to enter formula into cells in this column
and have them execute.

text format does NOT work for this feature, excel considers what is
entered as text and does NOT execute the formula.

.numberformat='general' will allow execution, but loses trailing zeros

How can I both retain trailing zeros and enter and execute formula into
the cells?

I guess I could
1) force format to text when a cell is selected
2) on change
check to see how many digits to the right of any decimal
place
and create a format #.00... that has the same number of digits
and
apply that to the cell.
if a formula is present[an = sign in first space], put it
back and
hope that it will now be evaluated.

Is there an easier way to do this?

Nobody has any suggestions?

This is what I have so far. Any suggestions as to improvements, shortcuts
or error handling that should be added?

------------------------------------------------------
Public Sub format_numbers(selection As Range)
'take text formatted numbers, determine precision,
' and then format to retain all significant digits
Dim target As Range 'targeted cell
Dim trstring As String 'trimmed string
Dim tstring As String 'test string
Dim posn As Integer 'position of decimal point
Dim precision As Integer 'places to right of decimal point
Dim fmt As String 'format string

Set target = selection
trstring = Trim(target.Text)
posn = InStr(1, trstring, ".")

If Len(trstring) > 0 Then
If Application.WorksheetFunction.IsNumber(Val(tstring)) Then
If InStr(1, trstring, ".") > 0 Then
tstring = Trim(Right(trstring, Len(trstring) - posn))
precision = Len(tstring)
fmt = "0." & String(precision, "0") 'format to retain precision
Else
fmt = "general" 'no decimal point, use general format
End If
Else
fmt = "@" 'not a number, format as text
End If
End If
target.Cells.NumberFormat = fmt
target.Cells.Value = trstring

End Sub




--
bz

please pardon my infinite ignorance, the set-of-things-I-do-not-know is an
infinite set.

(e-mail address removed) (remove ch100-5 to email)