Format number in userform textbox

Question

I have a texbox in a userform in which the user will input numbers. Those numbers will be tranfered to an Excel spreadsheet for calculation.

I want that when the user inputs numbers in the Textbox it displays as a number in a specific format.

For example: 2000000, I want it as 2,000,000.00 in the Textbox.

I tried:

Public Sub UserForm_Initialize()    
    TextBox1 = Format(TextBox1, "$#,##0.00")
End Sub

But the Userform doesn't change.

If I try the following

Private Sub TextBox1_Change()
    TextBox1 = Format(TextBox1, "$#,##0.00")
End Sub

The userform shows the number adequately, but then updates the Excel spreadsheet with a text format instead of number and the calculation doesn't run.

Answer 1

Here is a workaround, use the second subroutine to get the value in a string format.

Private Sub TextBox1_Change()
    TextBox1 = Format(TextBox1, "$#,##0.00")
End Sub

Then convert the format of the cell where this value was assigned. Range("A1").Value = CDbl(Range("A1").Value)