vba sum format number with currency

[maghari]

hi, experts
i need help to sum format number with currency
here i have userform textbox1 , 2 and 3
the codes is :


Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)TextBox1.Text = Format(TextBox1.Text, "#,##0.00")End Sub
Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)TextBox2.Text = Format(TextBox2.Text, "#,##0.00")End Sub
Private Sub TextBox3_Change()TextBox3.Text = Format(TextBox3.Text, "$#,##0.00")End Sub



when sum textbox1=1,300.00 and textbox2=1,500.00 it gives me textbox3=2.00 not 2,800.00
and when i add the currency like this in textbox1,2,3 ="$#,##0.00" it gives me error
i hope this help to under stand what i would

[SamT]

Private Sub TextBox3_Change()
TextBox3.Text = Format(TextBox3.Text, "$#,##0.00")
End SubThat is correct code. The issue is in your math sub

[maghari]

Private Sub TextBox3_Change()
TextBox3.Text = Format(TextBox3.Text, "$#,##0.00")
End SubThat is correct code. The issue is in your math sub


can you explain more i need the solution

[SamT]

Show us your math code that adds tbx1 and tbx2

[maghari]

Show us your math code that adds tbx1 and tbx2
this is my math code


TextBox3.Value = Val(TextBox1) + Val(TextBox2)

[Artik]

Private Sub Recalculate()
Dim ValTB1 As Double
Dim ValTB2 As Double

On Error Resume Next
ValTB1 = CDbl(TextBox1.Value)
ValTB2 = CDbl(TextBox2.Value)
On Error GoTo 0

TextBox3.Value = ValTB1 + ValTB2
End Sub
Artik

[maghari]

Private Sub Recalculate()
Dim ValTB1 As Double
Dim ValTB2 As Double

On Error Resume Next
ValTB1 = CDbl(TextBox1.Value)
ValTB2 = CDbl(TextBox2.Value)
On Error GoTo 0

TextBox3.Value = ValTB1 + ValTB2
End Sub
Artik


thanks but it gives me $0.00
attached my file

[Artik]

You changed the rules of the game during its duration. :motz2:

Remember that TextBox controls always store text. If you formatted the original number into a currency format, the TextBox contains the currency sign.
Either you give up the currency symbol in TextBox1 and TextBox2, or you need to get rid of this symbol before making calculations.

Artik

[maghari]

You changed the rules of the game during its duration. :motz2:

Remember that TextBox controls always store text. If you formatted the original number into a currency format, the TextBox contains the currency sign.
Either you give up the currency symbol in TextBox1 and TextBox2, or you need to get rid of this symbol before making calculations.

Artik

despite delete symbol of currency the problem is continued when i put theses values in textbox1,2 2,500 ,3500 it gives the result 5.00 not 6,000.00 you can try yourself

[p45cal]

You can keep the Formats as in msg#1 and tweak 2 code lines in Artik's code in msg#6:
ValTB1 = CDbl(Mid(TextBox1.Value, 2))
ValTB2 = CDbl(Mid(TextBox2.Value, 2))

[Artik]

Sorry, I suggested the US number format settings.
If the format is other than US try this code:
Private Sub CommandButton1_Click()
Dim ValTB1 As Double
Dim ValTB2 As Double


On Error Resume Next
ValTB1 = CDbl(TextBox1.Value)
ValTB2 = CDbl(TextBox2.Value)
On Error GoTo 0


TextBox3.Value = Format(ValTB1 + ValTB2)
End Sub


Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TextBox1.Text = Format(TextBox1.Text, "#,##0.00")
End Sub


Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TextBox2.Text = Format(TextBox2.Text, "#,##0.00")
End Sub


Private Sub TextBox3_Change()
TextBox3.Text = Format(TextBox3.Text, "$#,##0.00")
End Sub
or another solution with the option of choosing your own currency symbol (in the example $) or national currency symbol (variable strCurCode).
The example is a bit idiotic. :) I meant to show the possibility of using both symbols.
Option Explicit


Dim strCurCode As String




Private Sub CommandButton1_Click()
Dim ValTB1 As Double
Dim ValTB2 As Double


On Error Resume Next
ValTB1 = CDbl(ClearFormat(TextBox1.Value, "$"))
ValTB2 = CDbl(ClearFormat(TextBox2.Value, "$"))
On Error GoTo 0

'here I used the national currency symbol (strCurCode)
TextBox3.Value = f_CurrencyFormat(ValTB1 + ValTB2, strCurCode)
'TextBox3.Value = f_CurrencyFormat(ValTB1 + ValTB2, "$")
End Sub


Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TextBox1.Text = f_CurrencyFormat(TextBox1.Text, "$")
End Sub


Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TextBox2.Text = f_CurrencyFormat(TextBox2.Text, "$")
End Sub




Private Sub UserForm_Initialize()
strCurCode = Application.International(xlCurrencyCode)
End Sub




Private Function f_CurrencyFormat(ByVal strVal As String, Optional CurrencySign As String) As String
Dim dblVal As Double


strVal = Replace(strVal, CurrencySign, vbNullString)
On Error Resume Next
dblVal = CDbl(strVal)
On Error GoTo 0


f_CurrencyFormat = Format(dblVal, IIf(Len(CurrencySign) = 0, "", CurrencySign & " ") & "#,##0.00")


End Function




Private Function ClearFormat(ByVal strVal As String, Optional CurrencySign As String)
ClearFormat = Replace(strVal, CurrencySign, vbNullString)
End Function
Artik

[Paul_Hossler]

I think you can simplify a bit





Option Explicit


Private Sub CommandButton1_Click()
TextBox3.Value = FormatCurrency(CDbl(TextBox1.Value) + CDbl(TextBox2.Value))
End Sub


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


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

[SamT]

All UserForm Code
Option Explicit

Dim ValTB1 As Double
Dim ValTB2 As Double

Private Sub CommandButton1_Click()
TextBox3.Text = Format(ValTB1 + ValTB2, "$#,##0.00")
End Sub

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
ValTB1 = CDbl(TextBox1)
TextBox1.Text = Format(TextBox1.Text, "$#,##0.00")
End Sub

Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
ValTB2 = CDbl(TextBox2)
TextBox2.Text = Format(TextBox2.Text, "$#,##0.00")
End Sub

Private Sub UserForm_Click()

End Sub

[snb]

Don't use eventcode of elements to overcome the wrong choice of elements in a userform.
Only adapt the results if necessary


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Sheet1.Cells(2) = 0 + TextBox1 + TextBox2
Sheet1.Cells(2).NumberFormat = "€ 0.00"
End Sub

[p45cal]

FormatCurrency!
I'd completely forgotten (or never knew) that one!
Just one teeny tweak:
TextBox3.Value = FormatCurrency(CDbl(TextBox1.Value) + CDbl(TextBox1.Value))
to
TextBox3.Value = FormatCurrency(CDbl(TextBox1.Value) + CDbl(TextBox2.Value))

[Paul_Hossler]

There's also FormatDateTime, FormatNumber, and FormatPercent

I like them because they are aware of the computer's locale settings and can do some formatting

PS - thanks for the teeny tweak


https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/formatcurrency-function?f1url=https%3A%2F%2Fmsdn.microsoft.com%2Fquery%2Fdev11.query%3Fapp Id%3DDev11IDEF1%26l%3Den-US%26k%3Dk(vblr6.chm1008933)%3Bk(TargetFrameworkMoniker-Office.Version%3Dv16)%26rd%3Dtrue





FormatCurrency functionReturns an expression formatted as a currency value by using the currency symbol defined in the system control panel.
SyntaxFormatCurrency(Expression, [ NumDigitsAfterDecimal, [ IncludeLeadingDigit, [ UseParensForNegativeNumbers, [ GroupDigits ]]]])

The FormatCurrency function syntax has these parts:


Part
Description


Expression
Required. Expression to be formatted.


NumDigitsAfterDecimal
Optional. Numeric value indicating how many places to the right of the decimal are displayed. Default value is -1, which indicates that the computer's regional settings are used.


IncludeLeadingDigit
Optional. Tristate constant that indicates whether or not a leading zero is displayed for fractional values. See Settings section for values.


UseParensForNegativeNumbers
Optional. Tristate constant that indicates whether or not to place negative values within parentheses. See Settings section for values.


GroupDigits
Optional. Tristate constant that indicates whether or not numbers are grouped by using the group delimiter specified in the computer's regional settings. See Settings section for values.

[maghari]

Sorry, I suggested the US number format settings.
If the format is other than US try this code:
Private Sub CommandButton1_Click()
Dim ValTB1 As Double
Dim ValTB2 As Double


On Error Resume Next
ValTB1 = CDbl(TextBox1.Value)
ValTB2 = CDbl(TextBox2.Value)
On Error GoTo 0


TextBox3.Value = Format(ValTB1 + ValTB2)
End Sub

thanks you're both codes perfectly works


Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TextBox1.Text = Format(TextBox1.Text, "#,##0.00")
End Sub


Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TextBox2.Text = Format(TextBox2.Text, "#,##0.00")
End Sub


Private Sub TextBox3_Change()
TextBox3.Text = Format(TextBox3.Text, "$#,##0.00")
End Sub
or another solution with the option of choosing your own currency symbol (in the example $) or national currency symbol (variable strCurCode).
The example is a bit idiotic. :) I meant to show the possibility of using both symbols.
Option Explicit


Dim strCurCode As String




Private Sub CommandButton1_Click()
Dim ValTB1 As Double
Dim ValTB2 As Double


On Error Resume Next
ValTB1 = CDbl(ClearFormat(TextBox1.Value, "$"))
ValTB2 = CDbl(ClearFormat(TextBox2.Value, "$"))
On Error GoTo 0

'here I used the national currency symbol (strCurCode)
TextBox3.Value = f_CurrencyFormat(ValTB1 + ValTB2, strCurCode)
'TextBox3.Value = f_CurrencyFormat(ValTB1 + ValTB2, "$")
End Sub


Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TextBox1.Text = f_CurrencyFormat(TextBox1.Text, "$")
End Sub


Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TextBox2.Text = f_CurrencyFormat(TextBox2.Text, "$")
End Sub




Private Sub UserForm_Initialize()
strCurCode = Application.International(xlCurrencyCode)
End Sub




Private Function f_CurrencyFormat(ByVal strVal As String, Optional CurrencySign As String) As String
Dim dblVal As Double


strVal = Replace(strVal, CurrencySign, vbNullString)
On Error Resume Next
dblVal = CDbl(strVal)
On Error GoTo 0


f_CurrencyFormat = Format(dblVal, IIf(Len(CurrencySign) = 0, "", CurrencySign & " ") & "#,##0.00")


End Function




Private Function ClearFormat(ByVal strVal As String, Optional CurrencySign As String)
ClearFormat = Replace(strVal, CurrencySign, vbNullString)
End Function
Artik

you're both code work perfectly :yes

[maghari]

I think you can simplify a bit





Option Explicit


Private Sub CommandButton1_Click()
TextBox3.Value = FormatCurrency(CDbl(TextBox1.Value) + CDbl(TextBox2.Value))
End Sub


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


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





thanks paul your code well works:clap:

[maghari]

All UserForm Code
Option Explicit

Dim ValTB1 As Double
Dim ValTB2 As Double

Private Sub CommandButton1_Click()
TextBox3.Text = Format(ValTB1 + ValTB2, "$#,##0.00")
End Sub

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
ValTB1 = CDbl(TextBox1)
TextBox1.Text = Format(TextBox1.Text, "$#,##0.00")
End Sub

Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
ValTB2 = CDbl(TextBox2)
TextBox2.Text = Format(TextBox2.Text, "$#,##0.00")
End Sub

Private Sub UserForm_Click()

End Sub


your also code is excellent

[maghari]

thanks so much for every one help me to solve my problem i really enjoyed with this discussing

[Paul_Hossler]

It's a good way to learn

You can mark your thread SOLVED -- #3 in my sig