Excel User Form Number Formatting Problem

[JoannDavies]

I have a user form which has text boxes linked to cells in a spreadsheet. The cells in the spreadsheet are formatted just how I want the numbers to appear - no decimals and with a comma. The contents of the cells link just fine, I see the numbers I expect to see, but the formatting in the text box bears no relation to the formatting in the spreadsheet.

What's wrong???

Thanks for your help!

Joann

 

[rmikesmith]

Joann,

The linked TextBox uses the cell's value which is distinct from the applied formatting. To have the TextBox display a specific formatting, you will need to apply it using code. This can be place in the Change event procedure. Also, you will need to load the value through code (Userform's Initialize event) rather than setting the ControlSource property. Here is some sample code (assumes data source is cell A1):

Private Sub TextBox1_Change()
Exit Sub
  With TextBox1
    .Text = Format(.Text, "##,###")
  End With
End Sub

Private Sub UserForm_Initialize()
  With TextBox1
    .Text = ActiveSheet.Cells(1, 1).Text
  End With
End Sub

Actually, the Userform_Initialize code will display the same formatting as the cell because it accesses the Text property rather than the Value property. However, if the user makes changes in the TextBox the formatting will be lost without using something like the TextBox_Change code.

HTH
Mike

 

[JoannDavies]

Thanks, Mike, that sounds nice and simple and just what I need... I'll give it a try.

Joann