Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Macros et VBA Excel Discussion :

VBA - Fonction Worksheet_Change() ne fonctionne pas


Sujet :

Macros et VBA Excel

  1. #1
    Candidat au Club
    VBA - Fonction Worksheet_Change() ne fonctionne pas
    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 :Sélectionner tout -Visualiser dans une fenêtre à part
    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:

  2. #2
    Responsable
    Office & Excel

    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é.
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Une fois pour toutes, je donne mon avis. Je ne vais pas le répéter à chaque message...
    Si je propose une solution générique sur votre solution spécifique, c'est parce que, fainéant de nature, je privilégie le réutilisable...
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  3. #3
    Expert confirmé
    Bonjour,

    Citation Envoyé par d-ric Voir le message
    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.
    Cordialement,
    Patrice
    Personne ne peut détenir tout le savoir, c'est pour ça qu'on le partage.

    Pour dire merci, cliquer sur et quand la discussion est finie, penser à cliquer sur

  4. #4
    Responsable
    Office & Excel

    Salut Patrice,

    Citation Envoyé par Patrice740[ Voir le message

    É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 :Sélectionner tout -Visualiser dans une fenêtre à part
    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 :Sélectionner tout -Visualiser dans une fenêtre à part
    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.
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Une fois pour toutes, je donne mon avis. Je ne vais pas le répéter à chaque message...
    Si je propose une solution générique sur votre solution spécifique, c'est parce que, fainéant de nature, je privilégie le réutilisable...
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  5. #5
    Candidat au Club
    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 :Sélectionner tout -Visualiser dans une fenêtre à part
    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...

    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

  6. #6
    Responsable
    Office & Excel

    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...
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Une fois pour toutes, je donne mon avis. Je ne vais pas le répéter à chaque message...
    Si je propose une solution générique sur votre solution spécifique, c'est parce que, fainéant de nature, je privilégie le réutilisable...
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  7. #7
    Rédacteur

    Bonjour,
    Pour bien comprendre les procédures événementielles, je ne peux que conseiller la lecture de ces deux tutoriels
    Philippe Tulliez
    Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément. (Nicolas Boileau)
    Lorsque vous avez la réponse à votre question, n'oubliez pas de cliquer sur et si celle-ci est pertinente pensez à voter
    Mes tutoriels : Utilisation de l'assistant « Insertion de fonction », Les filtres avancés ou élaborés dans Excel
    Quelques contributions : USERFORM - Créer, Consulter, Modifier et Supprimer des enregistrements à l'aide d'un formulaire - Géolocalisation d'une adresse avec Excel et Google sans VBA

  8. #8
    Expert confirmé
    Bonjour,
    Citation Envoyé par d-ric Voir le message
    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.
    Cordialement,
    Patrice
    Personne ne peut détenir tout le savoir, c'est pour ça qu'on le partage.

    Pour dire merci, cliquer sur et quand la discussion est finie, penser à cliquer sur

  9. #9
    Responsable
    Office & Excel

    Citation Envoyé par Pierre Fauconnier Voir le message
    [...]
    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...
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Une fois pour toutes, je donne mon avis. Je ne vais pas le répéter à chaque message...
    Si je propose une solution générique sur votre solution spécifique, c'est parce que, fainéant de nature, je privilégie le réutilisable...
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  10. #10
    Expert éminent sénior
    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
    Chris

    Quand un homme a faim, mieux vaut lui apprendre à pêcher que de lui donner un poisson.
    Confucius

    ----------------------------------------------------------------------------------------------
    En cas de résolution, n'hésitez pas cliquer sur c'est toujours apprécié...

  11. #11
    Membre expérimenté
    Ce message n'a pas pu être affiché car il comporte des erreurs.
    Christophe (cavo789)
    Mes scripts Open Source : https://github.com/cavo789

  12. #12
    Candidat au Club
    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 :Sélectionner tout -Visualiser dans une fenêtre à part
    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.

    Le fichier Excel et la macro sur lesquels je travaille sont joints à cette réponse.

    Toute aide ou pistes sont la bienvenue!

  13. #13
    Expert éminent sénior
    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...
    Chris

    Quand un homme a faim, mieux vaut lui apprendre à pêcher que de lui donner un poisson.
    Confucius

    ----------------------------------------------------------------------------------------------
    En cas de résolution, n'hésitez pas cliquer sur c'est toujours apprécié...

  14. #14
    Candidat au Club
    Citation Envoyé par 78chris Voir le message
    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é:


    En effet je n'avais pas pensé à ça, merci. Je remplace donc par:
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    Private Sub Worksheet_Activate()
     
    End Sub

###raw>template_hook.ano_emploi###