Calculate math operations through textboes

[jonastiger]

Hi I have an issue in a userform I can't solve and need help for that, if possible.

When insert value in tb21 (IVA), return "Run-time error '13': Type mismatch
tb25 (VALOR C/DESCONTO) and tb27 (VALOR FINAL) show upsomething weird:
tb25 should return €44,62 (€52,50-€7,88)
tb27 should return €54,88 (€44,62+€10,26) - €10,26 is the result from tb21*tb25, if "...type mismatch" doesn't happen

Please see the image:


Herés the code:

Private Sub UserForm_Initialize()
    ' Add Items "UN" And "M2" to cb1
    cb1.AddItem "UN"
    cb1.AddItem "M2"
   
    ' Initialize TextBoxes
    tb16.Value = Format(0, "0.00")'COMP (cm)'
    tb17.Value = Format(0, "0.00")'LARG (m)'
    tb18.Value = Format(0, "0")'UNID'
    tb19.Value = Format(0, "€#,##0.00")'PREÇO UNIT'
    tb20.Value = Format(0, "0%")'DESCONTO'
    tb21.Value = Format(0, "0%")'IVA'
    tb22.Value = Format(0, "0.00")'ÁREA/UN'
    tb23.Value = Format(0, "€#,##0.00")'VALOR S/ DESCONTO'
    tb24.Value = Format(0, "€#,##0.00")'VALOR DESCONTO'
    tb25.Value = Format(0, "€#,##0.00")'VALOR COM DESCONTO'
    tb26.Value = Format(0, "€#,##0.00")'VALOR IVA'  
    tb27.Value = Format(0, "€#,##0.00")'VALOR FINAL'
   
    ' Set TextBoxes ForeColor
    tb16.ForeColor = &H808080
    tb17.ForeColor = &H808080
    tb18.ForeColor = &H808080
    tb19.ForeColor = &H808080
    tb20.ForeColor = &H808080
    tb21.ForeColor = &H808080
    tb22.ForeColor = &H808080
    tb23.ForeColor = &H808080
    tb24.ForeColor = &H808080
    tb25.ForeColor = &H808080
    tb26.ForeColor = &H808080
    tb27.ForeColor = &H808080
End Sub

Private Sub cb1_Change()
    If cb1.Value = "UN" Then
        tb16.Enabled = False
        tb17.Enabled = False
        tb18.Enabled = True
        tb16.BackColor = &HC0C0C0
        tb17.BackColor = &HC0C0C0
        tb18.SetFocus
    ElseIf cb1.Value = "M2" Then
        tb16.Enabled = True
        tb17.Enabled = True
        tb18.Enabled = False
        tb18.BackColor = &HC0C0C0
        tb16.SetFocus
    End If
End Sub

Private Sub tb16_Change()
    CalculateValues
End Sub

Private Sub tb17_Change()
    CalculateValues
End Sub

Private Sub tb18_Change()
    CalculateValues
End Sub

Private Sub tb19_Change()
    CalculateValues
End Sub

Private Sub tb20_Change()
    CalculateValues
End Sub

Private Sub tb21_Change()
    CalculateValues
End Sub

Private Sub CalculateValues()
    ' Tb22
    If tb16.Enabled And tb17.Enabled Then
        tb22.Value = Format((Val(tb16.Value) / 100) * (Val(tb17.Value) / 100), "0.00")
    ElseIf tb18.Enabled Then
        tb22.Value = Val(tb18.Value)
    End If
   
    ' tb23
    tb23.Value = Format(tb22.Value * Val(tb19.Value), "€#,##0.00")
   
    ' tb24
    If Val(tb20.Value) > 0 Then
        tb24.Value = Format(tb23.Value * Val(tb20.Value) / 100, "€#,##0.00")
    Else
        tb24.Value = Format(0, "€#,##0.00")
    End If
   
    ' tb25
    tb25.Value = Format(tb23.Value + tb24.Value, "€#,##0.00")
   
    ' tb26
    If Val(tb21.Value) > 0 Then
        tb26.Value = Format(tb25.Value * Val(tb21.Value) / 100, "€#,##0.00")
    Else
        tb26.Value = Format(0, "€#,##0.00")
    End If
   
    ' tb27
    tb27.Value = Format(tb25.Value + tb26.Value, "€#,##0.00")
End Sub

Thanks in advance
JT

 

[vletm]

jonastiger​

tb25 should return €44,62 (€52,50-€7,88)
but tb25.Value = Format(tb23.Value + tb24.Value, "€#,##0.00")
Have You verified - what are real values for all those tbs?
... before and after CalculateValues

 

[Marc L]

Hi, as a reminder textboxes are not numeric so just very not forget to convert them as numeric …​

So weird to over complicate with an UserForm instead of operating directly into a worksheet.​

​

 

[jonastiger]

jonastiger​

tb25 should return €44,62 (€52,50-€7,88)
but tb25.Value = Format(tb23.Value + tb24.Value, "€#,##0.00")
Have You verified - what are real values for all those tbs?
... before and after CalculateValues

in fact there is an error in

tb25.Value = Format(tb23.Value + tb24.Value, "€#,##0.00")

the correct is

tb25.Value = Format(tb23.Value - tb24.Value, "€#,##0.00")

but issues in tb21 (...type mismatch) and tb27 (VALOR FINAL) remain

 

[jonastiger]

Hi, as a reminder textboxes are not numeric so just very not forget to convert them as numeric …​

So weird to over complicate with an UserForm instead of operating directly into a worksheet.​

​

userform has the purpose for users not see some sensitive data

 

[Marc L]

the correct is

Still not ‼ Failing logic so back to post #2 and like I advised within my post #3 …​

​

As a reminder the TextBox Value property is exactly the same as its Text property​

so the + operator is equal to the & operator aka a string concatenation, far not a numeric sum !​

​

Another reminder as explained within VBA help : the Val function works only with the dot as decimal separator​

so you should better use a 'C' convert function like you can find in the same VBA help in 'See also' …​

​

userform has the purpose for users not see some sensitive data

As the same can be achieved directly & easily in a specific worksheet …​

​

 

[vletm]

jonastiger​

You skipped my question.
If You skip to verify - what are those?
... then how can You ask Excel to calculate something?
You seems to think that You're calculating as with sheet.
... if so, then - why do You use any Userform?
Above means almost same as written in #2 & #6 replies in other words.

If You really want to use Userform then one possible way would be something like below:
#1 Take care that only input textboxes are possible to edit
#2 Solve all inputs
eg tb16_ = tb16.value
and with same line, You should able to convert its value to usable (as well as verify that it is too)
#3 You could use those 'new' values for calculating.
#4 Take care that above 'new' values will use for textbox.values
eg tb23.value = tb23_ with correct format.

 

[Marc L]

According to VBA help - a must read ! - a demonstration of the bad, the ugly and the good :​

DefStr S

Sub Demo4Noob1()
    S1 = "€44,62"
    S2 = "€10,26"
    S3 = vbLf & vbLf
    MsgBox "1 :  " & S1 + S2 & S3 & "2 :  " & Val(S1) + Val(S2) & S3 & "3 :  " & CCur(S1) + CCur(S2), 64, "Results"
End Sub

As a reminder Val function well works only with "44.62€" …​

​