How to change text value to number?

[pruleone]

Hi

I made one excel, where you can add data through pop-up window.
Added data will be copied into excel sheet.
After that I'm making some of calculations etc.


Problem is that output from user-form is in text, so entered number 10.23 is text for excel and I can't use SUM formula to sum-up values.
If I look cell value then there is yellow exclamation mark and from menu can choose Convert to Number. After pressing it excel will start understand this field as number.

I have tried but haven't found solution how to convert this value to number automatically (with VBA).


So, help is needed

In userForm I have used following line / code to get data from user-form to excel sheet:
Worksheets("Data").Range("c2").Value = TextBox1
Worksheets("Data").Range("d2").Value = TextBox2

Maybe should change this (those) lines?


Also added file about what I'm talking.
PS! To activate user-form press button in page

 

[strongm]

Try not to rely on the default property of an object; it sometimes causes unexpected errors (in this case because two implicit casts are carried out).

Try

Worksheets("Data").Range("c2").Value = TextBox1[red].Value[/red]

 

[Andrzejek]

Try something like this:

If [blue]IsNumeric([/blue]TextBox1.Value[blue])[/blue] Then
    Worksheets("Data").Range("c2").Value = [blue]Val([/blue]TextBox1.Value[blue])[/blue]
Else
    MsgBox "Huston, we have a problem"
End If

You don't have to have this If-Then statement, but I would allow only numbers to be typed in the text box.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

And to take that one step further...

Worksheets("Data").Range("c2").Value = TextBox1.Value * 1

 

[pruleone]

Thank you

Used this solution:
Worksheets("Data").Range("c2").Value = TextBox1.Value * 1

Reasons:

Solution (TextBox1.Value) didn't change anything​

Solution (Val(TextBox1.Value)) did remove decimals, so should make this command longer with defining format as well​

Thank you

 

[Andrzejek]

Solution (TextBox1.Value) didn't change anything"
It wouldn't. Strongm just suggested avoiding the use of default property of an object, and instead state the property of your object in code.

“Val(TextBox1.Value)) did remove decimals”
Check your local setting for decimal sign.
If you have it set to . (period) this will work just fine:

Dim str As String
str = "123.44"
Debug.Print Val(str)

You get 123.44

But this will cut the stuff after the , (comma):

Dim str As String
str = "123,44"  '<- comma
Debug.Print Val(str)

You get 123

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[strongm]

>You get 123

Dependent on your regional settings

 

[combo]

VBA uses US settings, excel locals ones. To tell VBA to apply local rules you in text conversion you can use:
Worksheets("Data").Range("c2").FormulaLocal = TextBox1.text

combo