'calculs automatiques des textbox de Prix Total HT
Function pv(a): pv = Replace(Replace(a, ".", ","), " ", ""): End Function
Function vp(a): vp = Replace(Replace(Replace(a, ",", "."), " ", ""), Chr(160), ""): End Function
Function fo(a): fo = Format(a, "#,##0.00"): End Function
Sub TextBox_PuHT_1_Exit(ByVal Cancel As MSForms.ReturnBoolean): valeurs: End Sub
Sub TextBox_Qté_1_Exit(ByVal Cancel As MSForms.ReturnBoolean): valeurs: End Sub
Sub TextBox_PuHT_2_Exit(ByVal Cancel As MSForms.ReturnBoolean): valeurs: End Sub
Sub TextBox_Qté_2_Exit(ByVal Cancel As MSForms.ReturnBoolean): valeurs: End Sub
Sub TextBox_PuHT_3_Exit(ByVal Cancel As MSForms.ReturnBoolean): valeurs: End Sub
Sub TextBox_Qté_3_Exit(ByVal Cancel As MSForms.ReturnBoolean): valeurs: End Sub
Sub TextBox_PuHT_4_Exit(ByVal Cancel As MSForms.ReturnBoolean): valeurs: End Sub
Sub TextBox_Qté_4_Exit(ByVal Cancel As MSForms.ReturnBoolean): valeurs: End Sub
Sub TextBox_PuHT_5_Exit(ByVal Cancel As MSForms.ReturnBoolean): valeurs: End Sub
Sub TextBox_Qté_5_Exit(ByVal Cancel As MSForms.ReturnBoolean): valeurs: End Sub
Sub TextBox_PuHT_6_Exit(ByVal Cancel As MSForms.ReturnBoolean): valeurs: End Sub
Sub TextBox_Qté_6_Exit(ByVal Cancel As MSForms.ReturnBoolean): valeurs: End Sub
Sub TextBox_PuHT_7_Exit(ByVal Cancel As MSForms.ReturnBoolean): valeurs: End Sub
Sub TextBox_Qté_7_Exit(ByVal Cancel As MSForms.ReturnBoolean): valeurs: End Sub
Sub TextBox_PuHT_8_Exit(ByVal Cancel As MSForms.ReturnBoolean): valeurs: End Sub
Sub TextBox_Qté_8_Exit(ByVal Cancel As MSForms.ReturnBoolean): valeurs: End Sub
Sub TextBox_PuHT_9_Exit(ByVal Cancel As MSForms.ReturnBoolean): valeurs: End Sub
Sub TextBox_Qté_9_Exit(ByVal Cancel As MSForms.ReturnBoolean): valeurs: End Sub
Sub TextBox_PuHT_10_Exit(ByVal Cancel As MSForms.ReturnBoolean): valeurs: End Sub
Sub TextBox_Qté_10_Exit(ByVal Cancel As MSForms.ReturnBoolean): valeurs: End Sub
Sub TextBox_PuHT_11_Exit(ByVal Cancel As MSForms.ReturnBoolean): valeurs: End Sub
Sub TextBox_Qté_11_Exit(ByVal Cancel As MSForms.ReturnBoolean): valeurs: End Sub
Sub TextBox_PuHT_12_Exit(ByVal Cancel As MSForms.ReturnBoolean): valeurs: End Sub
Sub TextBox_Qté_12_Exit(ByVal Cancel As MSForms.ReturnBoolean): valeurs: End Sub
Sub valeurs()
Const TVA = 0.2 ' INDIQUER ICI le TAUX de TVA
Dim i&, x As Control, y As Control, z As Control, TotalHT
For i = 1 To 12
' on affecte à x,y,z les trois derniers controles de chaque ligne
Set x = Me.Controls("TextBox_Qté_" & i): Set y = Me.Controls("TextBox_PuHT_" & i)
Set z = Me.Controls("TextBox_PTHT_" & i)
' on en profite pour redéfinir les TabIndex
Me.Controls("TextBox_Réf_" & i).TabIndex = 4 * (i - 1) + 0
Me.Controls("TextBox_Désignation_" & i).TabIndex = 4 * (i - 1) + 1
x.TabIndex = 4 * (i - 1) + 2: y.TabIndex = 4 * (i - 1) + 3
' on prend le controle, on remplace les virgules par des points (vp)
' on trouve la valeur numérique (VAL), si c'est un montant monétaire
' on le formate(0:00 par fo),on remplace les points par des virgules (vp)
' on réaffecte le résultat au controle
If x <> "" Then x = pv(Val(vp(x)))
If y <> "" Then y = pv(fo(Val(vp(y))))
' si x ou y ne sont pas vide, on calcule le montant de la ligne et on l'inscrit
If Trim(x & y) <> "" Then z = pv(fo(Val(vp(x)) * Val(vp(y)))) Else z = ""
' on en profite pour rajouter le montant de la ligne au total hors taxe
TotalHT = TotalHT + Val(vp(z))
Next i
' on inscrit le total hors taxe
TextBox1_Mtt_total_HT = pv(fo(TotalHT))
' on inscrit le total TTC
TextBox_Mtt_TTC_commandé = pv(fo(TotalHT * (1 + TVA)))
End Sub