I want to store text such as '001234' in a cell. I have set the number format of this cell to text. After storing, an error 'Number Stored as Text' is shown in the form of a green triangle at the top left corner of the cell. It is very disturbing and I want it to be removed programmatically.
Add a comment
|
4 Answers
Consider:
Sub Macro1()
Application.ErrorCheckingOptions.NumberAsText = False
End Sub
This is a single line of a much larger macro to configure Excel when I begin working on a new computer.
3 Comments
Brian
+1 I tried looking for that a while ago. The following wouldn't work for me:
Application.Range(Item.Address).Errors.Item(xlNumberAsText).Ignore = True. Thank you!
Gary's Student
@Brian See my EDIT
Christian d'Heureuse
This changes the global Excel settings of the user, which is probably not a good idea to do in a general purpose application program.
The above examples turn off the "number stored as text" checking for the application.
This code will turn it off for a particular cell:
range("G93").Errors.Item(xlnumberastext).Ignore = True
Comments
You can select the range you want to work and then put for example (now added speechmarks):
Range("A1:Z20").Application.ErrorCheckingOptions.NumberAsText = False
2 Comments
Ron Rosenfeld
That seems to turn it off for the entire workbooks, and not limit it only to the particular range.
Ejaz Ahmed
I agree with your observation, because the Range object has an Application property which refers to the Application in which that range exists. That line is essentially the same as just typing Application.ErrorCheckingOptions.NumberAsText = False
Alternately, instead of formatting the cell as text, you could format the cell as 000000 to get the leading 0's to show.
1 Comment
Matti Wens
There may be good reason for storing the number as text, though, such as matching using VLOOKUP etc.