How to format a TextBox as currency

[AnalystGuy]

Here is my code....


Private Sub CommandButton1_Click()
With ActiveDocument
.SelectContentControlsByTitle("Date").Item(1).Range.Text = Me.TextBox1
.SelectContentControlsByTitle("FirstName").Item(1).Range.Text = Me.TextBox2
.SelectContentControlsByTitle("LastName").Item(1).Range.Text = Me.TextBox3
.SelectContentControlsByTitle("LastName2").Item(1).Range.Text = Me.TextBox3
.SelectContentControlsByTitle("CreditLimit").Item(1).Range.Text = Me.TextBox4
.SelectContentControlsByTitle("Entity").Item(1).Range.Text = Me.TextBox5
.SelectContentControlsByTitle("CreditScore").Item(1).Range.Text = Me.TextBox6
.SelectContentControlsByTitle("Creditdate").Item(1).Range.Text = Me.TextBox6
.SelectContentControlsByTitle("CreditLow").Item(1).Range.Text = Me.TextBox7
.SelectContentControlsByTitle("CreditHigh").Item(1).Range.Text = Me.TextBox8
.SelectContentControlsByTitle("Address").Item(1).Range.Text = Me.TextBox9
.SelectContentControlsByTitle("City").Item(1).Range.Text = Me.TextBox10
.SelectContentControlsByTitle("Creditdate").Item(1).Range.Text = Me.TextBox11
.SelectContentControlsByTitle("RelationshipManager").Item(1).Range.Text = Me.TextBox12
End With
End Sub


This code is within a userform CommandButton

There is a template I have set up and used RichText as placeholders to where the information will be sent. I need the Rich Textbox "CreditLimit" or the the TextBox4 to equal a currency value in the format of $1,000,000. Please tell me if this is possible and

[igotgame]

This is how I do it on one of my textboxes on a form (SalesPrice is my form textbox):


SalesPrice.Value = Format(SalesPrice.Value, "$###,##")


Then when they click Submit:


Private Sub SubmitButton_Click()
PurchasePrice = SalesPrice.Value
End Sub


I make PurchasePrice a Public Global variable so I can carry it over to my macro or macros from the form. So I mean this should work for what you need:

PurchasePrice = Format(PurchasePrice, "$###,##")

[AnalystGuy]

Thank you! Yeah that worked perfectly. I know this is probably beyond what VBA can do but is there anyone to have it pre populated with a $ sign and then also as the user is typing it automatically formats the text in the Userform to display what is is going to be displayed on the document?

[igotgame]

Thank you! Yeah that worked perfectly. I know this is probably beyond what VBA can do but is there anyone to have it pre populated with a $ sign and then also as the user is typing it automatically formats the text in the Userform to display what is is going to be displayed on the document?

Yes my form does this on my sheet...just gotta add a sub for that specific text box for when it changes so just double click the text box and put this:


Private Sub SalesPrice_Change()
SalesPrice.Value = Format(SalesPrice.Value, "$###,##")
End Sub


Now, to add a $ each time the form loads, you click the text box on your form and change the properties for "TEXT" and populate it with a "$" every time the form is loaded.

[AnalystGuy]

That worked.

Thanks again,

AnalystGuy

[cartercjb]

This is how I do it on one of my textboxes on a form (SalesPrice is my form textbox):


SalesPrice.Value = Format(SalesPrice.Value, "$###,##")


Then when they click Submit:


Private Sub SubmitButton_Click()
PurchasePrice = SalesPrice.Value
End Sub


I make PurchasePrice a Public Global variable so I can carry it over to my macro or macros from the form. So I mean this should work for what you need:


PurchasePrice = Format(PurchasePrice, "$###,##")

What is the proper currency format if we want to include 2 decimal points?

[Paul_Hossler]

I use

$#,##0.00_);($#,##0.00);$0.00_);@


positive;negative,zero;text

the _) says to leave a )'s amount of space for the postitive so that the ($1,234.56) will align

Textboxes show strings so I'd leave the actual dollar amount as a number (1234.56) and do the format at display time


There's also the FOrmatCurrency function

https://www.excelfunctions.net/vba-formatcurrency-function.html

which I believe in international aware