Help 'Number Stored as Text Error' !!!

[Peter]

I desperately need some help,

I have a VB app that populates a column with product numbers that are
numeric but are right filled with zeroes. ie. "006876".

If I set the cell value to "'006876" it works but I get the pesky green
triangle with a "number stored as text error". I don't want this, I
simply want it to be stored as a normal string.

I know I can click the button and select "ignore error", but I don't
want my users to have to do this every time.

Is there a way I can say " range.ignoreerror = true " or something like
that.

Any help would be much appreciated.

Thanks,
Peter

 

[Dave Peterson]

How about just turning that check off.

Tools|options|error checking tab
Uncheck all the ones you don't want.

 

[Peter]

Yes, i've done that, but I don't think my application should be taking
control of the user environment in excel. Other apps or the user may
want that check in other circumstances .

Any more ideas?

Peter

 

[Dave Peterson]

How about just resetting those errors:

Option Explicit
Sub testme01()
Dim myCell As Range
Dim myRng As Range
Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
With wks
Set myRng = Nothing
On Error Resume Next
Set myRng = .Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
On Error GoTo 0

If myRng Is Nothing Then
'do nothing
Else
For Each myCell In myRng.Cells
With myCell.errors(xlNumberAsText)
If .Value = True Then
.Ignore = True
End If
End With
Next myCell
End If
End With
Next wks

End Sub

 

[Peter]

Excellent, thanks Dave.