If you select all the cells  in the column  and the right click on the !
message icon, a menu will open that offers to conver them to numbers
for you.

How can this be done automatically?

It depends what you mean by "automatically"

If I have a number of cells  on a sheet which contain numbers  formatted as text
what I do is:

Type 1 in a blank cell
select the cell and copy it (ctrl-C)
select the cells to be changed
edit/paste special/multiply
OK

This seems to work.

If you want to get a macro to do it you could use a similar method. It would
depend on
- how you were going to identify the cells to be changed
- how the macro is to be triggered

The decision on the method to be used would be based on the frequency with
which you had to do this.

I have the same problem Lyman does... I have a form that requests
data from the user but it enters the data  into the field  formatted
as text. Error checking (the little triangle  in the cell) tells me
that it's a "Number Formatted as Text". But running a VBA routine to
change the number format  does not clear the text  formatting.

I also tried to record a macro for the manual "Convert to Number"
process you just described so I could grab the VBA code but it does
not perform the function. It sets the format to number but the error
checking is still there telling me that it is "Number Formatted as
Text".

What is the VBA function or statement that will do the "Convert to
Number" process? I think that is probably Lyman's real question too.
I even tried to clear the cell formatting and reformat it as a
number and it didn't work.

If there is no way to clear that formatting how do I set a the Text
form field to be a number instead of text? I don't see a property
for format in the field properties.

I cannot see an easy way to do this.

I am not an expert on userforms. However, I think what you may need to do is
not link the textbox on the userform to the cell, but put code in the useform
to cast the value of the textbox as a numeric  value (Clong for example) and
then set the cell in the worksheet to that value.

Thanks for the try on this but I was able to figure it out. It IS a
bit quirky but it works. Basically you apply the "PasteSpecial"
function with a multiply Operation. You have to have a "1" in a cell
somewhere for this to work  (I just put it in and hid the row). The
VBA code looks like this:

Private Sub FormFieldName_Change()
Worksheets("SheetName").Activate
Activesheet.Range("CellYouAreEnteringTo").Value = FormFieldName
Activesheet.Range("CellYouHave1in").Select
Selection.NumberFormat = "General"
Selection.Copy
Activesheet.pastespecial Paste:=xlPasteAll,
Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False
Selection.NumberFormat = "FormatYouWantNumbersToBe"

Just a slight restatement of the situation:

1) The information from the form is not put in the cell FORMATTED as text  -
it IS text information. With text information in a cell, Excel then ignores
any numeric  formatting you might have on the cell. Try creating a cell with
100000 in it, and format  it up with thousands separator and a couple of
decimal places. Now overtype the cell with "Fred" (no quotes) - the numeric
formatting is ignored. Type another number - the numeric formatting is
still there, it was just ignored for "Fred". Now type '123456 (note that
there is a single quote at the beginning and none at the end) - you'll get
no formatting, like with "Fred, and also the little green triangle  to nag
you.

Changing the number format - while you've got a text string in the cell -
won't help you. That's why your VBA wasn't working. Hopefully the above
explanation makes it clear why.

2) The little triangle is not actually error checking. In fact, the
situation you have is not actually an error (I use numbers  as text all the
time). It's simply an informative message to alert you to this situation.

So, the best way to do this is to get the form to put a numeric value into
the cell. This means that it will need to convert the text string from the
input box into a number and store it. Use the Val () function to do the
conversion.

Or, look at your VBA and do a conversion of the contents of the cell, not
its formatting. Something like

Range("I8").Formula = Val(Range("I8").Value)

I didn't have a proper number-as-text example to test, but this worked fine
for a cell with '123456 in it. (Actually, it also worked fine without the
"Val ()" and with any combinations of .Formula and .Value.) Have a play
with variations on this statement until you get one you like.