Format textbox in userform for currency

Peinecone

New Member
Joined
Oct 20, 2007
Messages
47
I have a 2 textboxes in a userform. One (txtCPU1) will display the result of a lookup, and the other (txtCost1) will display the result of a calculation.. How can I format the textboxs to have the values be in currency (ie $24.45) with the dollar sign and 2 decimals.
Here is the code right now for the change:

Code:
Private Sub txtCPU1_Change()
    
    If txtQty1.Value = "" Then Exit Sub
    If txtCPU1.Value = "" Then Exit Sub
    txtCost1.Value = CDbl(txtQty1.Value) * CDbl(txtCPU1.Value)
    
End Sub
The txtQty1 box is already limited to to a number, and the lookup value is a currency amount from a worksheet. Thanks for your help.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Try this:

Code:
txtCost1.Value = format(CDbl(txtQty1.Value) * CDbl(txtCPU1.Value), "$###,##.00")
 
Upvote 0
The last part of the code doesn't work.

Code:
txtCost1.Value = format(CDbl(txtQty1.Value) * CDbl(txtCPU1.Value), "$###,##.00")
 
Upvote 0
What are the data types for txtQty1 and txtCPU1?

If one of these is not formatted as a number, your calculation will not work.

Also, two other ideas:

1.) When using the format function, you probably don't need CDbl.
2.) Are you using the object in your actual code? userform1 example:

HTML:
If userform1.txtQty1.Value = "" Then Exit Sub
If userform1.txtCPU1.Value = "" Then Exit Sub
userform1.txtCost1.Value = format(userform1.txtQty1.Value * userform1.txtCPU1.Value), "$###,##.00")
<!-- / message -->
 
Last edited:
Upvote 0
Sorry, I had entered the code wrong.

But here is another problem. The box that is formated, is part of another sum formula. But it can't calculate because of the dollar sign. Is there a way to somehow exclude the dollar sign, or do I just have to live without it?
 
Upvote 0
Just create a variable for use in the next formula:

Code:
Dim x As Long
x = txtQty1.Value * txtCPU1.Value

Now you can also simplify your previous line of code:

Code:
txtCost1.Value = format(x, "$##,###.00")
 
Last edited:
Upvote 0
Here's another approach.
Code:
    txtCost1.Value = Format(txtQty1.Value * txtCPU1.Value, "Currency")
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top