TextBox Format/Editing Problem

I have a Userform that has several textboxes on it. The textboxes are linked to cells on a worksheet via the controlsource property. I also have formatted the textbox as a number with the following code:

Private Sub Textbox1_Change()

TextBox1.value = Format(TextBox1.value, "#,##0.00")

end sub

This works fine when the when the form activates, the textboxes have the cell values and the proper format. The problem is when the user tries to change a value in a textbox it will only let them input an amount between 0 and 9.99. I've tried different formats such as "#,###.##" and "0,000.00" with similar results.

Any help would be greatly appreciated.

This code will be invoked when ever cursor from textbox1 is moved to another control.


Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TextBox1.Text = Format(TextBox1.Text, "#,##,000.00")
End Sub