Problème avec Worksheet_Change

Bonjour à tous,

J'ai un soucis avec une fonction Worksheet_Change sur une macro.

Vous trouverez en pièce jointe mon fichier de calcul.

Si vous allez sur la feuille "1 - Siège" et que vous changez sur la ligne 24 l'épaisseur, mon Worksheet_Change s’exécute bien et change les valeur de la ligne 35 suivant la matière et l'épaisseur que j'ai choisi.

Si vous allez sur la feuille "2 - Opercule" et que vous changez sur la ligne 22 l'épaisseur, mon Worksheet_Change ne s’exécute pas complètement. Je n'arrive pas a tester en pas à pas un Worksheet_Change mais il semblerait qu'il n'arrive pas à réaliser le If Not Application.Intersect([Epaisseur_Commande], Range(Target.Address)) Is Nothing Then

Le code est pourtant un Copier/Coller de celui de la feuille "1 - Siège", les nom référencés sont les mêmes mais attribués par page donc tout devrait fonctionner sans soucis cela me rend fou. J'ai fait un test MsgBox ActiveSheet et la page active est bien celle ou je fais le changement, un MsgBox [Epaisseur_Commande] et il me donne bien l'épaisseur que j'ai choisi mais il semble ne pas voir quand je change la valeur.

Encore plus bizarre, quand je change la matière de la feuille "2 - Opercule" , le code s’exécute bien en entier donc il capte le changement d'épaisseur.

L'un de vous aurai-il une idée de ce qui peut se passer ? Peut-on tester pas a pas un Worksheet_Change ?

Bonjour Heelflip,

On peut tester un Worksheet_Change. Pour cela mettre l'instruction Stop après le Then.

Ne pas oublier de séparer par le :

Donc... Then : Stop: puis suite du code.

Salut X Cellus,

Merci pour l'info, je vais tester ça pour essayer de trouver ou ça ne va pas

OK merci pour le stop, cela m'a permis avec quelques MsgBox en plus de trouver ce qui n'allait pas.

Ce qui ne va pas c'est ce code: Il est censé lors d'un changement appliquer ce changement sur toutes les pages dont le CodeName commence par "Calcul" mais apparemment pour l'instant il ne le fait que sur la première page et ça franchement je n'ai pas encore compris pourquoi, car j'en ai trois qui ont ce type de nom et qui ont les mêmes variables...

Sub Worksheet_Change(ByVal Target As Range)

    'Déclaration des variables
    Dim WS As Object
    Dim Ta As String
    Dim Ts As String
    Dim Te As String

    If Not Application.Intersect([T_Ambiante], Range(Target.Address)) Is Nothing Then
        Ta = [T_Ambiante]
        For Each WS In Worksheets
            If Left(WS.CodeName, 6) = "Calcul" Then
                WS.[T_Ambiante] = Ta
            End If
        Next WS
    End If

    If Not Application.Intersect([T_Service], Range(Target.Address)) Is Nothing Then
        Ts = [T_Service]
        For Each WS In Worksheets
            If Left(WS.CodeName, 6) = "Calcul" Then
                WS.[T_Service] = Ts
            End If
        Next WS
    End If

    If Not Application.Intersect([T_Exceptionnelle], Range(Target.Address)) Is Nothing Then
        Te = [T_Exceptionnelle]
        For Each WS In Worksheets
            If Left(WS.CodeName, 6) = "Calcul" Then
                WS.[T_Exceptionnelle] = Te
                WS.[T_Test] = Te
            End If
        Next WS
    End If

    'Réinitialisation des objets
    Set WS = Nothing

End Sub

Bonjour Heelflip,

Il ne suffit pas de présenter un code, mais aussi (on ne le répète jamais assez...) de commenter son code.

Déjà, par exemple, la déclaration de variables n'a aucun commentaire. Même si on peut connaître leur type, il serait bon de savoir pour quelle raison elles sont définies. Ici WS est déclaré en Objet (trop général), or il n'est utilisé qu'en Worksheet. Donc autant le déclarer en Worksheet. Si les autres variables sont de type string, à quoi servent-elles?

De même T_Ambiante et autres du même genre, on peut indiquer leur utilité par un commentaire pertinent. Cela permet tout de suite de voir quel est le but d'une partie du code. Et d'un coup d'oeil de voir si le code inscrit est apte à fournir le résultat souhaité.

Bon sur ce rappel, je télécharge ton fichier. Et te demanderais sans doute des précisions.

A nouveau,

Il n'y a pas que ce souci apparemment, selon l'image ci-dessous.

Je ne pense pas qu'un nom puisse avoir une valeur

Suite,

J'ai changé par un module de test la valeur sur la 2ième feuille de calcul avec le nombre 40. On voit bien par le gestionnaire que celle-ci est bien modifiée.

Sauf qu'elle reste à 30 sur la feuille citée. Pourquoi utiliser un string alors que c'est un nombre qui est demandé?

Tous ces noms (T_Ambiante, etc...) ont une portée feuille, c'est à dire qu'il ne s'applique qu'au sein même de la feuille. Donne leur une portée Classeur.

A nouveau,

Les noms peuvent garder leur portée feuille, mais ils sont à différencier par un chiffre à droite du T sauf pour le premier. La boucle est supprimée, chaque feuille est directement ciblée. Ce code a été testé plusieurs fois et les 3 feuilles Calcul ont ressorti à l'identique le nombre choisi.

Penser à corriger la partie code de chaque feuille, car tout erreur dans celles-ci fera planter le report correct de l'entrée inscrite en feuille Résumé. Ainsi que l'arrêt du code pour débogage.

Sub Worksheet_Change(ByVal Target As Range)

    'Déclaration des variables
    Dim Ta As String
    Dim Ts As String
    Dim Te As String

    If Not Application.Intersect([T_Ambiante], Range(Target.Address)) Is Nothing Then
        Ta = [T_Ambiante]
Calcul_03.[T3_Ambiante]= Ta
Calcul_02.[T2_Ambiante] = Ta
 Calcul_01.[T_Ambiante] = Ta 
    End If

    'Même méthode que ci-dessus pour la suite du code... 
    'Réinitialisation des objets
    Set WS = Nothing

End Sub

Bonjour X Cellus,

Pourquoi les noms seraient à différencier s'il ont une portée feuille ? C'est justement l'inverse que je cherche, je vais finir avec 100 feuilles de calcul, si les noms en portée feuille sont les mêmes j'aurai un seul code pour l'ensemble du classeur, c'est justement à ça que sert une portée feuille.

J'ai pas mal bossé dessus. Au final cela fait bien ce que je veux maintenant, part contre, avec un Worksheet_Change sur la feuille "Calcul_01" et "Calcul_02" il execute deux fois le code sur toutes les feuilles, si j'ajoute le même Worksheet_Change sur la feuille "Calcul_03", il lancera trois fois les calculs, faut que je trouve ce qui fait cela.

Bonjour à tous ,

par contre, avec un Worksheet_Change sur la feuille "Calcul_01" et "Calcul_02" il execute deux fois le code sur toutes les feuilles, si j'ajoute le même Worksheet_Change sur la feuille "Calcul_03", il lancera trois fois les calculs,

Il faut garder à l’esprit qu’il faut prendre quelques précautions avec une procédure évènementielle de type Worksheet_Change.

Si vous modifiez une valeur de la feuille "Calcul_03", que se passe-t-il ? La procédure Worksheet_Change se lance, s’exécute et se termine.

Supposons maintenant qu’au cours de son exécution, cette procédure modifie à nouveau une valeur de la feuille "Calcul_03" , alors un nouvel évènement Worksheet_Change se produit (avant la fin de l’exécution). Cet évènement est intercepté et traité :

Une deuxième occurrence de Worksheet_Change est lancée après que les variables et registres de l’exécution aient été sauvegardés (ou empilés) pour pouvoir reprendre ensuite l’exécution de la première occurrence quand la seconde occurrence sera terminée.

Si la seconde occurrence modifie aussi les valeurs de la feuille "Calcul_03", une 3° occurrence sera lancée. Et le phénomène peut se reproduire à l’infini (enfin pas vraiment) :

1. Soit à un moment donnée les valeurs de la feuille Worksheet_Change ne sont plus modifiées et alors les occurrences de Worksheet_Change se terminent les unes après les autres
2. Soit on modifie à chaque fois les valeurs de Worksheet_Change et dans ce cas la « pile » va déborder (la mémoire de l’ordinateur quant à elle finie, si, si) et on aboutit à un crash ou blocage
3. soit une des occurrences se termine mais l’occurrence qui l'avait lancée en relance une autre. On entre dans une exécution sans fin.

Suivant le codage, des palliatif sont possibles. Par exemple l'utilisation d'un indicateur indiquant que si une une précédente occurrence est en cours d'exécution alors on sort immédiatement de l'actuelle occurrence sans rien faire ou bien une autre solution est de désactiver le traitement des évènements avant écriture sur la feuille "Calcul_03", écrire sur la feuille puis réactiver le traitement des évènements après écriture.

Les instructions qui font cela sont : Application.EnableEvents = False et Application.EnableEvents = True.

Attention ! Si une erreur se produit ou si on interrompt l’exécution après le Application.EnableEvents = False et avant le Application.EnableEvents = True, le traitement de tous les évènements sera interrompu => par la suite plus aucun évènement ne déclenchera Worksheet_Change (ni aucune autre procédure évènementielle)

Pour y remédier (entre autres) - si on arrive à reprendre la main sur Excel :

• Refermer Excel et le relancer
• Traiter dans le code les erreurs (ne suffit pas si on interrompt manuellement l’exécution)
• Aller dans VBE, tapez Ctrl+g pour ouvrir la fenêtre d’exécution, y saisir Application.EnableEvents = True puis la touche Entrée.

Nota : si ce n’est pas la procédure Worksheet_Change qui change les valeurs de la feuille "Calcul_03" directement mais si Worksheet_Change appelle une autre procédure qui elle-même change les valeurs de la feuille "Calcul_03", le même phénomène se produira.

Bonjour Heelflip, Mafraise,

Le principe donné vaut, au vu de tes trois feuilles présentes dans ton classeur. Et encore les codes des Worksheet_Change doivent être courts et/ou bien temporisés. Mais pour 100 feuilles de calculs tu te prépares des Nervous Breakdown.

Voir les remarques de Mafraise. L'évènement sera prioritaire sur la boucle que tu souhaites dans ta feuille Résumé. VBA ne peut être à la fois au four et au moulin.

Maintenant tu peux aussi prévoir une temporisation dans ta boucle qui ferait ainsi que tes feuilles calculs seraient bien toutes actualisées avec la valeur souhaitée mais avec des écarts de temps qui laisseraient le temps à d'autres opérations de se dérouler. Mais tu devra attendre...

Bonjour à vous deux.

Je pense en effet que c'est ce qui se passe. Car j'ai une redondance sur les mises à jour de températures que je ne peux pas vraiment laisser tomber. Je n'avais pas pensé au Application.EnableEvents, je vais tester avec cela.

Pour les 100 feuilles, j'y ai été fort, il y en aura 15/20, mais beaucoup de feuilles matières par contre. Donc le code ne devrait pas être trop lourd.