Sum up currency text boxes in userform decimal problem

[João Sequeira]

Hi,

I have a userform where you enter currency values into two text boxes (tVALUE & tVAT).
Upon adding values it should update a label (lTOTAL) with the sum of both text boxes.

The code works but I can't get decimal values to show up in lTOTAL, it always shows "value,00 €" and I can't understand why.

Here's my code:

Private Sub tVALUE_AfterUpdate()


If Not IsNumeric(tVALUE) And Len(tVALUE) > 0 Then
    MsgBox "Only currency values are allowed."
    tVALUE = ""
    tVAT = Format(tVAT, Number)
    lTOTAL = Val(tVALUE) + Val(tVAT)
    lTOTAL = Format(lTOTAL, "CURRENCY")
    tVAT = Format(tVAT, "CURRENCY")
    Exit Sub
        Else
        tVAT = Format(tVAT, Number)
        tVALUE = Format(tVALUE, Number)
        lTOTAL = Val(tVALUE) + Val(tVAT)
        lTOTAL = Format(lTOTAL, "CURRENCY")
        tVALUE = Format(tVALUE, "CURRENCY")
        tVAT = Format(tVAT, "CURRENCY")
End If


End Sub


Private Sub tVAT_AfterUpdate()


If Not IsNumeric(tVAT) And Len(tVAT) > 0 Then
    MsgBox "Only currency values are allowed."
    tVAT = ""
    tVALUE = Format(tVALUE, Number)
    lTOTAL = Val(tVALUE) + Val(tVAT)
    lTOTAL = Format(lTOTAL, "CURRENCY")
    tVALUE = Format(tVALUE, "CURRENCY")
    Exit Sub
        Else
        tVALUE = Format(tVALUE, Number)
        tVAT = Format(tVAT, Number)
        lTOTAL = Val(tVALUE) + Val(tVAT)
        lTOTAL = Format(lTOTAL, "CURRENCY")
        tVAT = Format(tVAT, "CURRENCY")
        tVALUE = Format(tVALUE, "CURRENCY")


End If

Thanks,
João

 

[João Sequeira]

Found answer in another post.

My problem is that my currency settings are "," for decimal separator and Val function doesn't recognize it as separator, it only recognizes ".".

So here's the code:

Private Sub tVALUE_AfterUpdate()

If Not IsNumeric(tVALUE) And Len(tVALUE) > 0 Then
    MsgBox "Only currency values are allowed."
    tVALUE = ""
    lTOTAL = Val(Replace(tVALUE, ",", ".")) + Val(Replace(tVAT, ",", "."))
    Exit Sub
        Else
        tVALUE = Format(tVALUE, "CURRENCY")
        lTOTAL = Val(Replace(tVALUE, ",", ".")) + Val(Replace(tVAT, ",", "."))
        lTOTAL = Format(lTOTAL, "CURRENCY")
End If


End Sub
Private Sub tVAT_AfterUpdate()


If Not IsNumeric(tVAT) And Len(tVAT) > 0 Then
       MsgBox "Only currency values are allowed."
    tVAT = ""
    lTOTAL = Val(Replace(tVALUE, ",", ".")) + Val(Replace(tVAT, ",", "."))
    Exit Sub
        Else
        tVAT = Format(tVAT, "CURRENCY")
        lTOTAL = Val(Replace(tVALUE, ",", ".")) + Val(Replace(tVAT, ",", "."))
        lTOTAL = Format(lTOTAL, "CURRENCY")
End If


End Sub

Thanks,
João