Formatting User Form Display

[Joe Glenn]

I have created a user form that receives its value from a NameRange in the workbook.

Private Sub UserForm_Initialize()

tb_TFR_PM_3PH_AMT.ControlSource = "TFR_PM_3PH_AMT"
tb_TFR_PM_3PH_AMT.Text = Format(tb_TFR_PM_3PH_AMT.Text, "#,##0.00")
MsgBox "Value = " & tb_TFR_PM_3PH_AMT.Text & " ."

End Sub

When the MsgBox displays the value is formatted correctly but when it displays on the UserForm it is 8156.5.

How can I display the number correctly on the UserForm?
Excel 2007 Windows XP

 

[Joe Glenn]

For all those individuals that have view this request this is how I finally solved the problem.

It boils down to where you put the Format command. I found that it works when you put the code on the textbox AfterUpdate sub.

Private Sub tb_AFUDC_AfterUpdate()
tb_AFUDC.Value = Format(Val(tb_AFUDC.Value), "###,###.##")
End Sub

The problem then becomes updating the database. The Val command stops read the data at the first comma. So instead of sending 6,500.00 to the database it sent 6. This is also a problem when trying to add two textbox values together to send to the database.

My solution to this problem was to create a variable as double in the sending routine and pass the variable instead of the text box.

Dim AFUDC As Double
AFUDC = tb_AFUDC.Value
Cells(LastRow, 20).Formula = CorpCap + AFUDC

Oh, isn't this more fun than Suduko.