PDA

View Full Version : [SOLVED] 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 :


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 SubThat is correct code. The issue is in your math sub

maghari
10-19-2019, 07:49 PM
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
10-20-2019, 12:51 AM
Show us your math code that adds tbx1 and tbx2

maghari
10-20-2019, 01:13 AM
Show us your math code that adds tbx1 and tbx2
this is my math 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
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. :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
10-20-2019, 06:13 AM
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
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/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
10-20-2019, 11:59 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

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
10-20-2019, 12:04 PM
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
10-20-2019, 12:08 PM
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

Paul_Hossler
10-20-2019, 02:47 PM
It's a good way to learn

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