Modifier une macro

Le 13/07/2020 à 13:50

salut à tous
je sais que le sujet revient sans cesse dans les forums mais je SUIS NOVICE ET JE NE COMPRENDS PAS TROP VBA.
j'ai fait une petite ligne de code vba mais lors de son exécution elle est très longue à s'inscrire dans le tableau
je me suis renseigné, je lai modifiée avec tous les sujets que j'ai vu mais rien n'y fait
si quelqu'un peut m'expliquer le problème.

voici ma macro :

Sub exemple()

    Application.ScreenUpdating = False

Private Sub CmdValider_Click()

    Range("B50000").End(xlUp).Offset(1, 0).Value = TextBox1
    Range("C50000").End(xlUp).Offset(1, 0).Value = TextBox2.Value
    Range("D50000").End(xlUp).Offset(1, 0).Value = TextBox3.Value
    Range("E50000").End(xlUp).Offset(1, 0).Value = TextBox4.Value
    Range("F50000").End(xlUp).Offset(1, 0).Value = TextBox5.Value
    Range("G50000").End(xlUp).Offset(1, 0).Value = TextBox6.Value
    Range("H50000").End(xlUp).Offset(1, 0).Value = TextBox7.Value
    Range("I50000").End(xlUp).Offset(1, 0).Value = TextBox8.Value
    Range("J50000").End(xlUp).Offset(1, 0).Value = TextBox10.Value
    Range("K50000").End(xlUp).Offset(1, 0).Value = TextBox11.Value
    Range("L50000").End(xlUp).Offset(1, 0).Value = TextBox12.Value
    Range("M50000").End(xlUp).Offset(1, 0).Value = TextBox13.Value
    Range("N50000").End(xlUp).Offset(1, 0).Value = TextBox14.Value
    Range("O50000").End(xlUp).Offset(1, 0).Value = TextBox9.Value
    Range("P50000").End(xlUp).Offset(1, 0).Value = TextBox15.Value
    Range("Q50000").End(xlUp).Offset(1, 0).Value = ComboBox1
    Range("R50000").End(xlUp).Offset(1, 0).Value = ComboBox2
    Range("S50000").End(xlUp).Offset(1, 0).Value = TextBox16.Value

End Sub

Private Sub TextBox1_Change()

TextBox1.Value = Format(Now, "dd/mm/yyyy")

End Sub

Private Sub ComboBox1_Change()

If ComboBox1 = "Cornettos" Then

TextBox3.Value = TextBox2.Value * (45 / 100)
TextBox4.Value = TextBox2.Value * (22.7 / 100)
TextBox5.Value = TextBox2.Value * (11.3 / 100)
TextBox6.Value = TextBox2.Value * (3.24 / 100)
TextBox7.Value = TextBox2.Value * (10.5 / 100)
TextBox8.Value = TextBox2.Value * (1.46 / 100)
TextBox10.Value = TextBox2.Value * (0.42 / 100)
TextBox11.Value = TextBox2.Value * (0.39 / 100)
TextBox12.Value = TextBox2.Value * (0.26 / 100)
TextBox13.Value = TextBox2.Value * (2.6 / 100)
TextBox14.Value = TextBox2.Value * (2.6 / 100)
TextBox9.Value = TextBox2.Value * (0.6 / 100)
TextBox15.Value = TextBox2.Value * (0.2 / 100)

End If

If ComboBox1 = "Tuiles" Then

TextBox3.Value = TextBox2.Value * (45 / 100)
TextBox4.Value = TextBox2.Value * (22.7 / 100)
TextBox5.Value = TextBox2.Value * (11.3 / 100)
TextBox6.Value = TextBox2.Value * (3.24 / 100)
TextBox7.Value = TextBox2.Value * (10.5 / 100)
TextBox8.Value = TextBox2.Value * (1.46 / 100)
TextBox10.Value = TextBox2.Value * (0.42 / 100)
TextBox11.Value = TextBox2.Value * (0.39 / 100)
TextBox12.Value = TextBox2.Value * (0.26 / 100)
TextBox13.Value = TextBox2.Value * (2.6 / 100)
TextBox14.Value = TextBox2.Value * (2.6 / 100)
TextBox9.Value = TextBox2.Value * (0.6 / 100)
TextBox15.Value = TextBox2.Value * (0.2 / 100)

End If

If ComboBox1 = "Frites" Then

TextBox3.Value = TextBox2.Value * (50 / 100)
TextBox4.Value = TextBox2.Value * (20 / 100)
TextBox5.Value = TextBox2.Value * (10 / 100)
TextBox6.Value = TextBox2.Value * (3 / 100)
TextBox7.Value = TextBox2.Value * (10 / 100)
TextBox8.Value = TextBox2.Value * (1.5 / 100)
TextBox10.Value = TextBox2.Value * (0.5 / 100)
TextBox11.Value = TextBox2.Value * (0.5 / 100)
TextBox12.Value = TextBox2.Value * (0.3 / 100)
TextBox13.Value = TextBox2.Value * (2.5 / 100)
TextBox14.Value = TextBox2.Value * (2.5 / 100)
TextBox9.Value = TextBox2.Value * (0.5 / 100)
TextBox15.Value = TextBox2.Value * (0.2 / 100)

End If

Application.ScreenUpdating = True 'Facultatif

End Sub

Private Sub CmdAnnuler_Click()

Unload Me

End Sub

Private Sub UserForm_Click()

End Sub

merci de vos réponses


edit Dan : titre de sujet modifié. Merci de veiller à mettre un titre qui correspond à votre demande

Le 13/07/2020 à 14:03

Bonjour,

A quel évènement la lenteur de ta macro ?

Et ceci cela sort d'où ?

Sub exemple()

    Application.ScreenUpdating = False

Cette commande tu dois la mettre juste après tes private sub et inscrire l'inverse de cette commande à la fin de chaque sub.

    

Private Sub CmdValider_Click()
    Application.ScreenUpdating = False

    Range("B50000").End(xlUp).Offset(1, 0).Value = TextBox1
    Range("C50000").End(xlUp).Offset(1, 0).Value = TextBox2.Value
    Range("D50000").End(xlUp).Offset(1, 0).Value = TextBox3.Value
    Range("E50000").End(xlUp).Offset(1, 0).Value = TextBox4.Value
    Range("F50000").End(xlUp).Offset(1, 0).Value = TextBox5.Value
    Range("G50000").End(xlUp).Offset(1, 0).Value = TextBox6.Value
    Range("H50000").End(xlUp).Offset(1, 0).Value = TextBox7.Value
    Range("I50000").End(xlUp).Offset(1, 0).Value = TextBox8.Value
    Range("J50000").End(xlUp).Offset(1, 0).Value = TextBox10.Value
    Range("K50000").End(xlUp).Offset(1, 0).Value = TextBox11.Value
    Range("L50000").End(xlUp).Offset(1, 0).Value = TextBox12.Value
    Range("M50000").End(xlUp).Offset(1, 0).Value = TextBox13.Value
    Range("N50000").End(xlUp).Offset(1, 0).Value = TextBox14.Value
    Range("O50000").End(xlUp).Offset(1, 0).Value = TextBox9.Value
    Range("P50000").End(xlUp).Offset(1, 0).Value = TextBox15.Value
    Range("Q50000").End(xlUp).Offset(1, 0).Value = ComboBox1
    Range("R50000").End(xlUp).Offset(1, 0).Value = ComboBox2
    Range("S50000").End(xlUp).Offset(1, 0).Value = TextBox16.Value

Application.ScreenUpdating = True 'Facultatif
End Sub

Private Sub TextBox1_Change()

TextBox1.Value = Format(Now, "dd/mm/yyyy")

End Sub

Private Sub ComboBox1_Change()
Application.ScreenUpdating = False

If ComboBox1 = "Cornettos" Then

TextBox3.Value = TextBox2.Value * (45 / 100)
TextBox4.Value = TextBox2.Value * (22.7 / 100)
TextBox5.Value = TextBox2.Value * (11.3 / 100)
TextBox6.Value = TextBox2.Value * (3.24 / 100)
TextBox7.Value = TextBox2.Value * (10.5 / 100)
TextBox8.Value = TextBox2.Value * (1.46 / 100)
TextBox10.Value = TextBox2.Value * (0.42 / 100)
TextBox11.Value = TextBox2.Value * (0.39 / 100)
TextBox12.Value = TextBox2.Value * (0.26 / 100)
TextBox13.Value = TextBox2.Value * (2.6 / 100)
TextBox14.Value = TextBox2.Value * (2.6 / 100)
TextBox9.Value = TextBox2.Value * (0.6 / 100)
TextBox15.Value = TextBox2.Value * (0.2 / 100)

End If

If ComboBox1 = "Tuiles" Then

TextBox3.Value = TextBox2.Value * (45 / 100)
TextBox4.Value = TextBox2.Value * (22.7 / 100)
TextBox5.Value = TextBox2.Value * (11.3 / 100)
TextBox6.Value = TextBox2.Value * (3.24 / 100)
TextBox7.Value = TextBox2.Value * (10.5 / 100)
TextBox8.Value = TextBox2.Value * (1.46 / 100)
TextBox10.Value = TextBox2.Value * (0.42 / 100)
TextBox11.Value = TextBox2.Value * (0.39 / 100)
TextBox12.Value = TextBox2.Value * (0.26 / 100)
TextBox13.Value = TextBox2.Value * (2.6 / 100)
TextBox14.Value = TextBox2.Value * (2.6 / 100)
TextBox9.Value = TextBox2.Value * (0.6 / 100)
TextBox15.Value = TextBox2.Value * (0.2 / 100)

End If

If ComboBox1 = "Frites" Then

TextBox3.Value = TextBox2.Value * (50 / 100)
TextBox4.Value = TextBox2.Value * (20 / 100)
TextBox5.Value = TextBox2.Value * (10 / 100)
TextBox6.Value = TextBox2.Value * (3 / 100)
TextBox7.Value = TextBox2.Value * (10 / 100)
TextBox8.Value = TextBox2.Value * (1.5 / 100)
TextBox10.Value = TextBox2.Value * (0.5 / 100)
TextBox11.Value = TextBox2.Value * (0.5 / 100)
TextBox12.Value = TextBox2.Value * (0.3 / 100)
TextBox13.Value = TextBox2.Value * (2.5 / 100)
TextBox14.Value = TextBox2.Value * (2.5 / 100)
TextBox9.Value = TextBox2.Value * (0.5 / 100)
TextBox15.Value = TextBox2.Value * (0.2 / 100)

End If

Application.ScreenUpdating = True 'Facultatif

End Sub

Private Sub CmdAnnuler_Click()

Unload Me

End Sub

Private Sub UserForm_Click()

End Sub
Le 13/07/2020 à 17:29

J'ai trouvé cette commande ici : https://www.excel-pratique.com/fr/astuces_vba/screenupdating

Le 15/07/2020 à 08:34
mateous33 a écrit :

J'ai trouvé cette commande ici : https://www.excel-pratique.com/fr/astuces_vba/screenupdating

Oui mais elle est mal placée dans ton code.

Rechercher des sujets similaires à "modifier macro"