vba sum format number with currency

**maghari** · 10-19-2019, 12:11 PM

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

HTML Code:

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** · 10-19-2019, 03:29 PM

Private Sub TextBox3_Change()
TextBox3.Text = Format(TextBox3.Text, "$#,##0.00")
End Sub

That is correct code. The issue is in your math sub

**maghari** · 10-19-2019, 07:49 PM

Originally Posted by SamT

Private Sub TextBox3_Change()
TextBox3.Text = Format(TextBox3.Text, "$#,##0.00")
End Sub

That is correct code. The issue is in your math sub

can you explain more i need the solution

**SamT** · 10-20-2019, 12:51 AM

Show us your math code that adds tbx1 and tbx2

**maghari** · 10-20-2019, 01:13 AM

Originally Posted by SamT

Show us your math code that adds tbx1 and tbx2

this is my math code

HTML Code:

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

**Artik** · 10-20-2019, 03:51 AM

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** · 10-20-2019, 05:02 AM

Originally Posted by 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

thanks but it gives me $0.00
attached my file

**Artik** · 10-20-2019, 05:26 AM

You changed the rules of the game during its duration.

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** · 10-20-2019, 06:13 AM

Originally Posted by Artik

You changed the rules of the game during its duration.

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** · 10-20-2019, 07:41 AM

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** · 10-20-2019, 08:50 AM

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** · 10-20-2019, 09:16 AM

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** · 10-20-2019, 09:19 AM

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** · 10-20-2019, 09:34 AM

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** · 10-20-2019, 09:41 AM

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** · 10-20-2019, 11:23 AM

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/off...6)%26rd%3Dtrue

FormatCurrency function

Returns an expression formatted as a currency value by using the currency symbol defined in the system control panel.
Syntax

FormatCurrency(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** · 10-20-2019, 11:59 AM

Originally Posted by 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

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

**maghari** · 10-20-2019, 12:04 PM

Originally Posted by 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

thanks paul your code well works

**maghari** · 10-20-2019, 12:08 PM

Originally Posted by 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

your also code is excellent

**maghari** · 10-20-2019, 12:20 PM

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

Thread: vba sum format number with currency

Thread Tools

Display

vba sum format number with currency

Posting Permissions