VBA - Fonction Worksheet_Change() ne fonctionne pas

Version imprimable

1 pièce(s) jointe(s)

Bonjour le forum!

Débutant dans le monde vaste de la VBA, j'ai récupéré (rendons à César ce qui lui appartient) un code me permettant de colorer les cases d'une feuille appelée "calendrier" selon les dates des activités définies dans la feuille "Activités".
J'aimerais que la macro (reliée à la feuille "Calendrier") se mette à jour dès qu'une action (n'importe laquelle) s'opère sur la feuille "Activité".

La fonction Worksheet_Change() me semble alors toute trouvée. Mais je ne vois pas comment lui indiquer qu'elle doit se relancer lorsque qu'une action s'opère sur la feuille "Activités".

Y aurait une âme chaleureuse pour m'aiguiller et me permettre de débugger le reste de ma fonction? :?

Voici le code en question:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False debPlan = DateSerial(2020, 1, 1) Set fActivités = Sheets("Activités") Set fCalendrier = Sheets("Calendrier") [D6:JUC32].ClearContents [D6:JUC32].Interior.ColorIndex = xlNone nbactivités = fActivités.[B1].CurrentRegion.Rows.Count For i = 3 To nbactivités Responsable = fActivités.Cells(i, 4) Set Result = fCalendrier.[C:C].Find(What:=Responsable, LookIn:=xlValues) If Not Result Is Nothing Then If fActivités.Cells(i, 3) < DateSerial(2040, 1, 1) Then ddébut = fActivités.Cells(i, 5) dfin = fActivités.Cells(i, 7) Libellé = fActivités.Cells(i, 2) fCalendrier.Cells(Result.Row, ddébut) = Libellé lig = Result.Row For d = ddébut To dfin fCalendrier.Cells(Result.Row, d).Interior.ColorIndex = 6 Next d End If End If Next i End Sub
Et le fichier Excel:
Pièce jointe 579670

20/09/2020, 16h50
Pierre Fauconnier

Salut.

Tu dois utiliser l'évènement Worksheet_Change de la feuille Activités...

Perso, je n'aime pas mettre du code applicatif dans le code évènementiel. Je conseille de détacher l'applicatif au sein d'une procédure d'un module standard ou du module de classe de la feuille, et de l'appeler par le code évènementiel...

Note toutefois que l'évènement Change n'est pas levé sur "n'importe quelle action" d'une feuille mais lorsque que le contenu de cellule est modifié, par l'utilisateur ou par VBA. Cela exclut le cas où la valeur de la cellule est modifiée suite à un recalcul). On parle bien de Valeur. Si tu modifies la décoration d'une cellule (couleur, police, cadre, ...), l'évènement n'est pas levé.
20/09/2020, 16h52
Patrice740

Bonjour,

Citation:

Envoyé par d-ric

J'aimerais que la macro (reliée à la feuille "Calendrier") se mette à jour dès qu'une action (n'importe laquelle) s'opère sur la feuille "Activité".
La fonction Worksheet_Change() me semble alors toute trouvée.

Eh bien non , pas n'importe quelle action !

Comme son nom l'indique, l'évènement change ne se produit que lorsque le contenu d'une des cellules de la feuille change :
- en manuel lorsqu'on quitte le mode édition du contenu de cellule en le validant ou lors d'un collage.
- par macro lorsqu'on modifie la valeur ou, la formule d'une (ou plusieurs) cellule(s).
Mais il ne se produit pas dans les autres cas, par exemple quand le résultat des calculs change ou la couleur change ou ....

Note :
- on entre en mode édition de cellule par F2 ou clic dans la barre de formule ou double clic dans la cellule.
- on valide le contenu par Tab ou Entrée ou Maj+Tab ou Maj+ Entrée ou dans certains cas en cliquant dans une autre cellule (méthode déconseillée).
- on quitte sans valider avec Esc.

Édit : pour compléter le billet de Pierre :
- il est possible de transmettre Target aux procédures du module standard
- je ne vois pas pourquoi on utiliserait l'évènement Change si on n'utilise pas Target.

Salut Patrice,

Citation:

Envoyé par Patrice740[...

Édit : pour compléter le billet de Pierre :
- il est possible de transmettre Target aux procédures du module standard
- je ne vois pas pourquoi on utiliserait l'évènement Change si on n'utilise pas Target.

Oui, bien sûr. L'intérêt de Worksheet_Change, c'est de tester quelle cellule a été modifiée (ou à quelle plage elle appartient). Dès lors, on transfèrera Target à la procédure applicative. Voici comment je conçois les choses, les procédures UpdateA1A10 et SetHello pouvant exister dans le module de classe de la feuille ou dans un module standard:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("a1:a10")) Is Nothing Then UpdateA1A10 Target If StrComp(Target(1, 1).Value, "bonjour", vbTextCompare) = 0 Then SetHello Target End Sub Sub UpdateA1A10(Target As Range) MsgBox "vous avez modifié la cellule " & Target.Address End Sub Sub SetHello(Target As Range) MsgBox "Vous avez dit bonjour en " & Target.Address End Sub
Je vois souvent le code suivant, mais perso, je n'aime pas parce que le code évènementiel ne devrait rien faire d'applicatif, notamment parce que le débogage est malaisé. Ce code résulte pour moi d'un problème d'architecture. L'exemple est évidemment plus pertinent lorsqu'il y a plusieurs lignes dans les blocs If... End If et si chaque bloc If..End If a besoin de ses propres variables (voir le billet cité plus haut).
Code:

1 2 3 4 5 6 7 8 Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("a1:a10")) Is Nothing Then MsgBox "vous avez modifié la cellule " & Target.Address End If If StrComp(Target(1, 1).Value, "bonjour", vbTextCompare) = 0 Then MsgBox "Vous avez dit bonjour en " & Target.Address End If End Sub
I l n'y a donc que des avantages à utiliser une bonne architecture. Cette architecture permet également de factoriser du code et d'écrire une fois le code utilisé par plusieurs feuilles et ou plusieurs cellules/plages.

Bonsoir messieurs,

N'étant pas aussi calé que vous en VBA, il y a beaucoup de choses que je n'arrive pas à saisir...

J'ai appliqué ce code à la feuille "Activités" de telle sorte à ce que la macro se relance dès qu'une cellule de la plage A3:G10 est modifiée (selon les critères que vous avez annoncés):

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
Private Sub Worksheet_Change(ByVal Target As Range)
 
    Set Target = Range("A3:G10")
 
    Application.ScreenUpdating = False
    debPlan = DateSerial(2020, 1, 1)
    Set fActivités = Sheets("Activités")
    Set fCalendrier = Sheets("Calendrier")
        [D6:JUC32].ClearContents
        [D6:JUC32].Interior.ColorIndex = xlNone
    nbactivités = fActivités.[B1].CurrentRegion.Rows.Count
    For i = 3 To nbactivités
    Responsable = fActivités.Cells(i, 4)
    Set Result = fCalendrier.[C:C].Find(What:=Responsable, LookIn:=xlValues)
    If Not Result Is Nothing Then
        If fActivités.Cells(i, 3) < DateSerial(2040, 1, 1) Then
            ddébut = fActivités.Cells(i, 5)
            dfin = fActivités.Cells(i, 7)
            Libellé = fActivités.Cells(i, 2)
            fCalendrier.Cells(Result.Row, ddébut) = Libellé
            lig = Result.Row
            For d = ddébut To dfin
                fCalendrier.Cells(Result.Row, d).Interior.ColorIndex = 6
            Next d
        End If
    End If
    Next i
 
End Sub

Mais lorsque je change une date, le fichier mouline puis se ferme...8O

Une architecture propre m'interesserait vu que je vais être amené à utiliser d'autres macros dans le même classeur. Mais j'aimerais dans un premier réussir à faire fonctionner cette macro.

A vous relire,
Cordialement

20/09/2020, 20h14
Pierre Fauconnier

il ne faut pas modifier Target. Target, c'est la cellule qui est modifiée et dont tu captures l'évènement de modification.

Regarde comment j'ai travaillé avec Intersect pour déterminer si la cellule modifié fait partie de la plage considérée ou pas.

Après, si le code modifie une autre cellule de la même feuille, il convient d'utiliser Application.EnableEvents = False avec une gestion d'erreur pour ne pas mouliner en boucle.

A ce stade, il serait intéressant que tu précises, en français et sans jargon Excel, ce que tu souhaites réaliser...
20/09/2020, 20h20
Philippe Tulliez
Bonjour,
Pour bien comprendre les procédures événementielles, je ne peux que conseiller la lecture de ces deux tutoriels
- Les évènements dans la feuille de calcul Excel
- Les évènements du module objet ThisWorkbook, dans Excel
20/09/2020, 20h29
Patrice740

Bonjour,

Citation:

Envoyé par d-ric

Une architecture propre m'interesserait vu que je vais être amené à utiliser d'autres macros dans le même classeur. Mais j'aimerais dans un premier réussir à faire fonctionner cette macro.

Mauvaise méthode ...
On met en place une architecture propre avant de développer le détail des procédures ou des fonction,
autrement dit, on commence par analyser le problème avant d'essayer de le résoudre !

Et avant de se lancer dans le VBA, on essaie de résoudre le problème avec les fonctionnalités natives du tableur.
20/09/2020, 20h34
Pierre Fauconnier

Citation:

Envoyé par Pierre Fauconnier

[...]
A ce stade, il serait intéressant que tu précises, en français et sans jargon Excel, ce que tu souhaites réaliser...

Bis Repetita... Explique ce que tu veux obtenir, pas le moyen d'y arriver (ça, c'est notre affaire...). Autrement dit, ton code, on s'en fout. On te donnera LE code lorsque l'on saura ce qu'il doit faire... ;)
21/09/2020, 00h25
78chris

Bonjour à tous

Il serait bien d'utiliser sur l'onglet Activités un tableau structuré et non une plage avec des formules sur des lignes vides. Entre autres avantages, cela facilite aussi le codage

Un tableau structuré ne doit pas garder son nom automatique, mais comme les onglets et les classeurs, avoir un nom signifiant et ne pas contenir de lignes vides, règles que ne respecte pas ton tableau de l'onglet Données

Pas sûr que la conception du classeur soit idéale

Si on reste sur VBA, je pense qu'il faudrait arriver à différencier l'ajout d'une ligne de la modification et ne pas refaire tout le planning à chaque modif d'autant qu'en ajout rien de peux être fait tant qu'on n'a pas saisi assez d'informations, et qu'en modification cela parait lourd de changer pour chaque modif d'une même ligne car cela risque de provoquer autant de reconstructions du planning que de modifications de cellules pour une ligne.

Il faudrait prévoir plutôt une mise à jour à la demande ou à l'activation de l'onglet Planning.

Avec une version Excel un peu moins vieille (13 ans quand même ...) on pourrait utiliser PowerQuery pour le planning
21/09/2020, 07h41
cavo789

Bonjour

Avec Worksheet_Change *chaque* modification faite sur la sheet va entraîner l'exécution de ta fonction; tu modifies 15 valeurs, hop, 15 exécutions. D'où un point d'attention à ne jamais perdre de vue : l'optimisation.

Si ton code est vraiment basique (p.ex. mettre la cellule en rouge); sache que tu peux le faire avec les "Conditional formatting" (je ne connais pas le terme exact en français, formatage conditionnel je suppose). Tu peux comparer des valeurs entre elles et plein d'autres choses; je te recommande d'y jeter un oeil; ce sera toujours terriblement plus optimisé que du code VBA qui contient deux boucles (combien d'itérations?) et qui fait un reset à chaque fois. Au fûr et à mesure que ta sheet deviendra plus complexe, tu vas petit à petit tuer les perfs.

Bonne journée.
21/09/2020, 18h24
d-ric

1 pièce(s) jointe(s)
Bonjour Pierre,

J'ai testé ta macro et c'est un peu plus clair dans son utilisation et l’intérêt de séparer les 2 fonctions:
Code:

1 2 3 Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1:W10")) Is Nothing Then UpdatePlan Target End Sub
Le fichier excel est composé de 3 feuilles:
-1 feuille "Etudes": sur cette feuille, 1 ligne correspond à 1 tache (ex: Maintenance PAC) qui est rattachée à un Responsable avec
une date de début et de fin de la tache.
-1 feuille "Planning": sur cette feuille, je mets sous forme de planning horizontal les taches par responsable en colorant les cellules
entre la date de début et de fin.
-1 feuille "Données": Elle sert à de base de données pour la mise en forme du planning et le calcul des jours ouvrés.

La macro me permet de mettre en forme les donnnées de la feuille Etudes pour avoir une vue linéaire et temporelle de toutes les études
reliées à un responsable. J'ai 2 critères de mise en forme:

- Les cellules entre la date de début et de fin de l'étude sont colorées selon le code couleur défini dans la feuille "Données"
- Faire afficher le nom de l'étude sur la première cellule d'une plage colorée.

Et elle met à jour le planning dès qu'une case de la feuille "Etudes" est modifiée.
Pièce jointe 579787
Le fichier Excel et la macro sur lesquels je travaille sont joints à cette réponse.

Toute aide ou pistes sont la bienvenue!
21/09/2020, 18h38
78chris

RE

Comme déjà dit plutôt que de mettre à jour dès qu'on touche une cellule, ce qui sera ingérable dès que le volume va augmenter, plutôt le faire à l'activation de la feuille ou mieux, à partir de 2010, une solution PowerQuery

A noter que tes numéros de semaines sont à la norme américaine, ce qui donnera une semaine fausse en 2021...
21/09/2020, 19h20
d-ric

1 pièce(s) jointe(s)
Citation:

Envoyé par 78chris

RE

Comme déjà dit plutôt que de mettre à jour dès qu'on touche une cellule, ce qui sera ingérable dès que le volume va augmenter, plutôt le faire à l'activation de la feuille ou mieux, à partir de 2010, une solution PowerQuery

A noter que tes numéros de semaines sont à la norme américaine, ce qui donnera une semaine fausse en 2021...

Bien vu, merci! J'ai corrigé:
Pièce jointe 579790

En effet je n'avais pas pensé à ça, merci. Je remplace donc par:
Code:

1 2 3 Private Sub Worksheet_Activate() End Sub