Bug macro worksheet_change en boucle

Question

Encore une fois, j'ai besoin de votre aide précieuse. Voilà mon problème. Je possède dans mon fichier une base de données que j'ai restreinte à 3 colonnes. colonne 1 : numéro national (identifiant unique) colonne 2 : numéro de travail colonne 3: nom de l'individu (des vaches en l’occurrence ...) Je souhaiterai, pour chaque ligne, qu'à la saisie d'un de ces 3 éléments les deux autres s'affichent automatiquement si la vache a déjà été renseignée une première fois et que les données sont donc disponibles dans la base de données. J'ai donc réalisé une macro avec worksheet_change. Seulement elle s'applique sur les 3 colonnes et donc à chaque fois qu'elle inscrit une valeur dans une des cellules, elle se recharge et donc tourne en boucle... du moins c'est l'impression que j'ai. Le soucis c'est que je ne veux pas bloquer la saisie sur une seule colonne mais bien laisser le choix à l'utilisateur en fonction de l'élément qu'il souhaite saisir (à priori numéro de travail ou nom). Les "1" en colonne A servent à savoir si la vache a déjà été renseigné, la macro n'est censée se déclencher que sur cette condition. A terme, il faudra que j'insère une fonction sur cette colonne. J'apprends sur le tas en VBA et mon code n'est surement pas très "beau".... Pour info, au départ dans mon code, je faisais les recherches index/equiv directement en VBA mais j'ai changé parce que ça ramait. Je suis donc passé en "FormulaLocal=". Merci d'avance pour votre aide...

galopin01 · Accepted Answer

Effectivement les macros Worksheet_Change se déclanchent en boucle si elles modifient une cellule dans la feuille. Ordinairement une macro Worksheet_Change commence et se termine comme celà : Private Sub worksheet_change(ByVal target As Range) If Target.Count = 1 Then '... longue suite de code varié End if End Sub Ce qui permet que la macro de ne se déclancher que si une seule cellule est modifiée. Impossible de déclancher cette macro avec un Copier/Coller sur plusieurs lignes/colonnes (Ce qui introduit la plupart du temps une erreur) Pour la protection contre toute erreur imprévue on rajoute ensuite : Private Sub worksheet_change(ByVal target As Range) If Target.Count = 1 then On Error GoTo GESTERR '... longue suite de code varié End if Exit Sub GESTERR: Application.EnableEvents = True End Sub Enfin encadrant ton code, à chaque fois que tu places une instruction qui modifie une cellule tu mets aussitot avant Application.EnableEvents = False et aussitot après Application.EnableEvents = True Ce qui dans ton exemple et pour la première boucle IF donne If Not Intersect(target, Range("B5:B150")) Is Nothing Then If target &lt;&gt; "" Then If target.Offset(0, -1).Value = 1 Then Application.EnableEvents = False Range("A1").Value = target.Value target.Offset(0, 1).FormulaLocal = "=INDEX(C5:C150;EQUIV(A1;B5:B150;0);1)" target.Offset(0, 2).FormulaLocal = "=INDEX(D5:D150;EQUIV(A1;B5:B150;0);1)" Application.EnableEvents = True End If End If End If Bien sur il est préférable de faire la recherche directement en VBA et dans ce cas tu procèderas de la même façon Placer les instructions Application.EnableEvents =... de par et d'autre des lignes qui modifient une cellule dans la feuille. Il est très important de prendre pour habitude de placer ces instructions au plus près des lignes sensibles afin de circonscrire les risques d'erreurs. L'instruction Application.EnableEvents = False inhibe la gestion des énènements d'Excel et par suite toi tu perds tout contrôle sur ton prog et même sur eXCEL si tu n'as pas correctement mis en place la gestion d'erreur rappelée plus haut. Dans ce cas (absence de gestion d'erreur) la punition est inévitable : Excel ne répond plus. Pas de possibilité de sauvegarde. On éteint l'ordinateur (ou on passe par le gestionnaire de tâche pour fermer Excel) et on perd toutes les modifs non enregistrées. La punition est la même si tu fais du débogage et que tu décides d'interrompre la macro après une instruction Application.EnableEvents = False mais avant son antidote : Application.EnableEvents = True En résumé si tu as bien appliqué mes commentaires ton code devrait ressembler à ça : (les commentaires inutiles n'ont pas lieu d'être... Private Sub worksheet_change(ByVal Target As Range) If Target.Count = 1 Then On Error GoTo GESTERR Application.ScreenUpdating = False If Not Intersect(Target, Range("B5:B150")) Is Nothing Then If Target &lt;&gt; "" Then If Target.Offset(0, -1).Value = 1 Then Application.EnableEvents = False 'inhibe la gestion d'évènements Range("A1").Value = Target.Value Target.Offset(0, 1).FormulaLocal = "=INDEX(C5:C150;EQUIV(A1;B5:B150;0);1)" Target.Offset(0, 2).FormulaLocal = "=INDEX(D5:D150;EQUIV(A1;B5:B150;0);1)" Application.EnableEvents = True 'rétablit la gestion d'évènements End If End If End If If Not Intersect(Target, Range("C5:C150")) Is Nothing Then If Target &lt;&gt; "" Then If Target.Offset(0, -2).Value = 1 Then Application.EnableEvents = False 'inhibe la gestion d'évènements Range("A1").Value = Target.Value Target.Offset(0, -1).FormulaLocal = "=INDEX(B5:B150;EQUIV(A1;C5:C150;0);1)" Target.Offset(0, 1).FormulaLocal = "=INDEX(D5:D150;EQUIV(A1;C5:C150;0);1)" Application.EnableEvents = True 'rétablit la gestion d'évènements End If End If End If If Not Intersect(Target, Range("D5:D150")) Is Nothing Then If Target &lt;&gt; "" Then If Target.Offset(0, -3).Value = 1 Then Application.EnableEvents = False 'inhibe la gestion d'évènements Range("A1").Value = Target.Value Target.Offset(0, -2).FormulaLocal = "=INDEX(B5:B150;EQUIV(A1;D5:D150;0);1)" Target.Offset(0, -1).FormulaLocal = "=INDEX(C5:C150;EQUIV(A1;D5:D150;0);1)" Application.EnableEvents = True 'rétablit la gestion d'évènements End If End If End If Exit Sub GESTERR: 'rétabli le fonctionnement d'Excel avant de quitter Application.EnableEvents = True End Sub Hum... j'ai fait l'impasse sur des observations mineures pour ne pas noyer le poisson ! A+...