Simplifier code vba Le sujet est résolu

Y compris Power BI, Power Query et toute autre question en lien avec Excel
M
MARK_TK
Membre habitué
Membre habitué
Messages : 122
Inscrit le : 12 janvier 2016
Version d'Excel : 2013

Message par MARK_TK » 25 mars 2016, 19:26

Bonsoir Je fais un code vba pour mon projet
je vois ce code peux moins simplifier et courte pour tout les autres lignes

ce code au desous est pour seulement 2 lignes, mais j'ai 13 lignes

merci d'avance
Private Sub TextBox1_Change()
Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim D As Integer
Dim B1 As Integer
Dim C1 As Integer


If TextBox1.Value <> "" And TextBox13.Value <> "" And TextBox37.Value <> "" And TextBox121.Value <> "" And TextBox25.Value <> "" And TextBox49.Value <> "" Then
A = TextBox1.Value
B = TextBox13.Value
C = TextBox37.Value
B1 = TextBox25.Value
C1 = TextBox49.Value
D = TextBox121.Value
TextBox61.Value = (A - B) + ((0.5 * D) - C)
TextBox73.Value = (A - B1) + ((0.5 * D) - C1)
End If
End Sub
Private Sub TextBox25_Change()
Dim A As Integer
Dim B1 As Integer
Dim C1 As Integer
Dim D As Integer

If TextBox1.Value <> "" And TextBox25.Value <> "" And TextBox49.Value <> "" And TextBox121.Value <> "" Then
A = TextBox1.Value
B1 = TextBox25.Value
C1 = TextBox49.Value
D = TextBox121.Value
TextBox73.Value = (A - B1) + ((0.5 * D) - C1)

End If
End Sub
Private Sub TextBox49_Change()
Dim A As Integer
Dim B1 As Integer
Dim C1 As Integer
Dim D As Integer

If TextBox1.Value <> "" And TextBox25.Value <> "" And TextBox49.Value <> "" And TextBox121.Value <> "" Then
A = TextBox1.Value
B1 = TextBox25.Value
C1 = TextBox49.Value
D = TextBox121.Value
TextBox73.Value = (A - B1) + ((0.5 * D) - C1)

End If
End Sub
Private Sub TextBox13_Change()
Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim D As Integer



If TextBox1.Value <> "" And TextBox13.Value <> "" And TextBox37.Value <> "" And TextBox121.Value <> "" Then
A = TextBox1.Value
B = TextBox13.Value
C = TextBox37.Value
D = TextBox121.Value
TextBox61.Value = (A - B) + ((0.5 * D) - C)

End If
End Sub
Private Sub TextBox37_Change()
Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim D As Integer

If TextBox1.Value <> "" And TextBox13.Value <> "" And TextBox37.Value <> "" And TextBox121.Value <> "" Then

A = TextBox1.Value
B = TextBox13.Value
C = TextBox37.Value
D = TextBox121.Value
TextBox61.Value = (A - B) + ((0.5 * D) - C)

End If
End Sub

Private Sub TextBox2_Change()
Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim B1 As Integer
Dim C1 As Integer
Dim D As Integer

If TextBox2.Value <> "" And TextBox14.Value <> "" And TextBox38.Value <> "" And TextBox121.Value <> "" And TextBox26.Value <> "" And TextBox50.Value <> "" Then
A = TextBox2.Value
B = TextBox14.Value
C = TextBox38.Value
B1 = TextBox26.Value
C1 = TextBox50.Value
D = TextBox121.Value

TextBox62.Value = (A - B) + ((0.5 * D) - C)
TextBox74.Value = (A - B1) + ((0.5 * D) - C1)
End If
End Sub
Private Sub TextBox26_Change()
Dim A As Integer
Dim B1 As Integer
Dim C1 As Integer
Dim D As Integer

If TextBox2.Value <> "" And TextBox26.Value <> "" And TextBox50.Value <> "" And TextBox121.Value <> "" Then
A = TextBox2.Value
B1 = TextBox26.Value
C1 = TextBox50.Value
D = TextBox121.Value

TextBox74.Value = (A - B1) + ((0.5 * D) - C1)
End If
End Sub
Private Sub TextBox50_Change()
Dim A As Integer
Dim B1 As Integer
Dim C1 As Integer
Dim D As Integer

If TextBox2.Value <> "" And TextBox26.Value <> "" And TextBox50.Value <> "" And TextBox121.Value <> "" Then
A = TextBox2.Value
B1 = TextBox26.Value
C1 = TextBox50.Value
D = TextBox121.Value

TextBox74.Value = (A - B1) + ((0.5 * D) - C1)
End If
End Sub
Private Sub TextBox14_Change()
Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim D As Integer

If TextBox2.Value <> "" And TextBox14.Value <> "" And TextBox38.Value <> "" And TextBox121.Value <> "" Then
A = TextBox2.Value
B = TextBox14.Value
C = TextBox38.Value
D = TextBox121.Value

TextBox62.Value = (A - B) + ((0.5 * D) - C)
End If
End Sub

Private Sub TextBox38_Change()
Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim D As Integer

If TextBox2.Value <> "" And TextBox14.Value <> "" And TextBox38.Value <> "" And TextBox121.Value <> "" Then
A = TextBox2.Value
B = TextBox14.Value
C = TextBox38.Value
D = TextBox121.Value

TextBox62.Value = (A - B) + ((0.5 * D) - C)
End If
End Sub
Mark_test_1.jpg
Avatar du membre
eriiic
Passionné d'Excel
Passionné d'Excel
Messages : 8'684
Appréciations reçues : 292
Inscrit le : 7 février 2010
Version d'Excel : 2010fr

Message par eriiic » 25 mars 2016, 19:41

Bonjour,

pour simplifier moi je mettrai une listbox de 7 colonnes x 13 lignes
eric
En essayant continuellement, on finit par réussir.
Donc plus ça rate, plus on a de chances que ça marche.
(les Shadoks)

En plus du merci (si si, ça se fait !!!), penser à mettre en résolu. Merci
M
MARK_TK
Membre habitué
Membre habitué
Messages : 122
Inscrit le : 12 janvier 2016
Version d'Excel : 2013

Message par MARK_TK » 25 mars 2016, 19:59

Merci eriic mais comment ?
voila mon travail comment avec ton aider de simplifier le tableau
merci d'avance
P_B_VBA_01.xlsm
(29.12 Kio) Téléchargé 5 fois
Répondre Sujet précédentSujet suivant
  • Sujets similaires
    Réponses
    Vues
    Dernier message