Userform textbox number format

Question

Hi!

I have a userform with a textbox and a commandbutton. The textbox value must be written on a cell. Both the textbox and the cell, must show a number with thousand separator and no decimal places. The thing is that I don't know if I should use the Format function or the CDbl function, because one workmate says I must write the code like this:

Application.Goto ActiveWorkbook.Sheets("sheet2").Cells(8, 3) 'Enter data

ActiveCell.FormulaR1C1 = Format(TextBox1, "#")

Private Sub TextBox1_Change()

TextBox1 = Format(TextBox1, "#,###")

End Sub

---

And the other one tells me I must write it like this, because it avoids the internation setting issue and works with a wider range of numbers:

Application.Goto ActiveWorkbook.Sheets("sheet2").Cells(8, 3) 'Enter data

ActiveCell.FormulaR1C1 = CDbl(TextBox1)

Private Sub TextBox1_Change()

TextBox1 = Format(TextBox1, "#,###")

End Sub

Which is the right way to go? When I did it with the first code, in the begining it was like this Format(TextBox1, "#,###") The problem was the cell showed a warning asking if I wanted to convert the value to a number. Then I asked how could I store the textbox value on the cell without being a string and having to convert it to a number. My workmate told me to write it like this: Format(TextBox1, "#")

Then other workmate came and told me that using the VB CDbl function was much better, but now I'm confused.

Thanks

Answer 1

I ran into the same issue.  Try multiplying the value by 1.  Not sure why it works, but it does.

Private Sub TextBox1_Change()

TextBox1 = Format(TextBox1.value * 1,"#,###")

Cells(8, 3) = TextBox1

End sub

Answer 2

Thanks, Mike but it doesn't work:

• It shows the textbox value as text and the warning message tells me to convert it to number. What I need is that when I click on the commandbutton the cell stores the textbox value as number with thousand separator and not as text.
• The userform textbox shouls show the thousands separator as well.

Hi,

I'm at a complete loss to understand why Excel would warn you to convert the value to a number other than to guess you have some code here you haven't told is about.

I uploaded a working version to my Skydrive, link below, that formats the text box and places that value onto the worksheet as a number, it's called userform_Formatting

https://skydrive.live.com/?cid=66a66ea84229b01b&sa=680348902#cid=66A66EA84229B01B&id=66A66EA84229B01B%21108

Answer 3

Thanks, Mike but it doesn't work:

• It shows the textbox value as text and the warning message tells me to convert it to number. What I need is that when I click on the commandbutton the cell stores the textbox value as number with thousand separator and not as text.
• The userform textbox shouls show the thousands separator as well.

Answer 4

Hi,

To begin with you should avoid selecting anything and to do this you don't need to. Text boxes do return text strings but you don't need to convert to double or anything else to do this,

With ActiveWorkbook.Sheets("sheet2").Cells(8, 3)

    .NumberFormat = "#,##0"

    .Value = TextBox1.Text

End With

This will keep the underlying value