Number Formats In A UserForm TextBox

[Minitman]

Greetings,

I have a TextBox on a UserForm that I need to format as currency with
the Exit event. I can't seem to find what the correct syntax is for
this to happen.

Anyone have any ideas on how this should look?

TIA

-Minitman

 

[Frank Kabel]

Hi
if you want this as string value have a look at the format method. e.g.
var=format(textbox1.value,"0.00 $")

 

[Sharad Naik]

Follwoing code will format it to $1,234.56 format:
Put it in to TextBox Exit event.

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

Sharad

 

[Tom Ogilvy]

You don't need to double-double quote the $ sign

s# = 1234.56
? Format(s, """$""#,##0.00")
$1,234.56
? format(s,"$ #,##0.00")
$ 1,234.56

I added a space in the second, but that makes no difference: (just for
clarity)
? format(s,"$#,##0.00")
$1,234.56

so for the OP

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Textbox1.Text = format(cdbl(Textbox1.Text),"$ #,##0.00")
End Sub

or (cdbl is optional - excel will coerce the string)

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Textbox1.Text = format(Textbox1.Text,"$ #,##0.00")
End Sub

 

[Sharad Naik]

Yes, for standard currency symbol no need to double-dobule quote. For
currency symbol not appearing in the cell number format in excel, double
quote is needed.

Sharad

 

[Tom Ogilvy]

Not exactly true either. As long as the characters used are not formatting
characters, there is no need:

? format(1234.56,"AB#,##0.00")
AB1,234.56


AB certainly isn't a standard currency symbol.

ABC causes problems:
? format(1234.56,"ABC#,##0.00")
AB5/18/1903 1:26:24 PM#,##0.00

but you can fix that with
? format(1234.56,"AB\C#,##0.00")
ABC1,234.56

 

[Minitman]

Hey Tom,

Thanks for the answer and the lesson. It always helps to understand
how the answer works.

-Minitman