Formatting a VBA Form as Currency

[robert_shindorf]

This has to be VBA 101... but....

can I format a textbox in a VBA form that when I input 500 and go to the next box, it formats it as a currency and displays "$500.00"

i'm sure it's super easy, i just don't know the steps.

[mikerickson]

I'd put

TextBox1.Text = Format(TextBox1.Text, "$#,##0.00")

in the After_Update routine.

[robert_shindorf]

ok, here it is, i was thinking it would be much easier, but i can't seem to get it.

there are 6 categories that i need to format as a currency, they are:


Assigned Fee
Billing Rate
Tax Strategies Fee
Transactional Tax Fee
Business Valuation Fee
Website Fee

here's the workbook, i'm pretty lost.

[royUK]

Use the textbox exit event to format the contents

Private Sub txtAsgnFee_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With Me
If Len(.txtAsgnFee.Value) > 0 Then .txtAsgnFee.Value = Format(txtAsgnFee.Value, _
    "Currency")
End With
End Sub

[shg]

I just learned that you can use format names in a Format function -- my Excel lesson for the day, thanks, Roy.

You could generalize Roy's approach a tad:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    FormatCurrency Me.TextBox1
End Sub

Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    FormatCurrency Me.TextBox2
End Sub

Sub FormatCurrency(ctl As MSForms.TextBox)
    If Len(ctl.Value) Then ctl.Value = Format(ctl.Value, "Currency")
End Sub