"Peter Beach" wrote in message
...
Hi Howard,

Don't see the original post :-(

Do you mean something like:

Sub b()
Dim s As String

s = Format$(1.2345, "$0.000")
Debug.Print s
End Sub

or

Sub c()
Dim s As String

s = Format$(1234568, "00-00-00-00")
Debug.Print s
End Sub

Perhaps I'm missing something. As I say I can't see your original post.


The following is the original post.
---------------------------
I am inserting stuff into some cells as follows:

shtExcel.Range(Cells(lngRow, 1), Cells(lngRow, 5)) = Split(strBuffer, vbTab)

Then, I change the cells in columns 2-5 to numeric formattrying to change
some of the cells to numeric format. using code like

With shtExcel.Range(Cells(lngFormat, 2),Cells(lngFormat + lngHigh, 3))
.Value = .Value
End With

Then assigning the relevant format for the cells in eaxh column.

However, the cells in column 1 are supposed to retain Text format, but some
cells have all numbers, e.g., 000, and are intended to stay as Text.

If I set Application.ErrorCheckingOptions.NumberAsText = false, I achieve my
goal while the workbook is open.
However, if I save the workbook, exit Excel, then re-open the workbook, the
NumberAsText propety reverts to whatever the user has chosen.

How can I programmatically change numbers to Text format?
If I save the value in a string variable, then assign to the Value property,
the cell is treated as a number, e.g., 000 becomes 0.

Is there a way to do the deed without changing a user's NumberAsText
property?

------------------------------
And I followed this with the following post:
------------------------------------------
I tried

.Range(Cells(lngFormat, 1), Cells(lngFormat + lngHigh,
1)).NumberFormat = "@"
For i = 0 To lngHigh
With Cells(lngFormat + i, 1)
If IsNumeric(.Value) Then
strTemp = CStr(.Value)
.Value = "'" & strTemp
End If
End With
Next i

Excel still insists on marking the cells as errors.

The only real issue is how to programmatically tell Excel to ignore the
"error" for those cells.
Is there a way to do that?

The NumberAsText property can be overridden by the user's options, not to
mention the property is not supported by Excel 97 and 2000.