Problème de coloration de cellules en masse en VBA

Salut à tous.

J'ai un problème avec quelques lignes de code.

Dans ce code, je veux que lorsque je modifie la valeur d'une cellule d'une feuille, la couleur de remplissage soit modifiée par rapport à des couleurs définies dans une autre feuille.

Par exemple, si je renseigne la lettre C, je veux que la couleur soit verte, si je supprime le C, je veux que la case redevienne blanche.

Tant que je change la valeur de mes cellules une à une, tout va bien, ça marche, mais lorsque je modifie plusieurs cellules (sélection d'une zone de cellule puis Ctrl+Entrée), j'ai une erreur 13 : Incompatibilité de type, qui apparait. Idem si je supprime le contenu de plusieurs cellules d'un coup.

Lorsque je débug, il m'amène sur la ligne 12 du code ci dessous :

La ligne en cause !

   If Target = "" Then

Le code complet !

Private Sub Worksheet_Change(ByVal Target As Range)

' retirer la protection de la feuille

ActiveSheet.Unprotect

   a = 50

'si le DPX supprime un libellé dans l'onglet pointage -> mettre en blanc le fond

   If Target = "" Then
         ligne = Target.Row
           colo = Target.Column
            Feuil1.Cells(ligne, colo).Select
               With Selection.Interior
                    .Pattern = xlNone
                    .TintAndShade = 0
                    .PatternTintAndShade = 0
                End With
                ' protéger la feuille

        ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        False, AllowFiltering:=True
            Exit Sub
    Else

   End If

 'si le DPX ajoute un libellé dans l'onglet pointage -> mettre en couleur le fond en fonction du choix

   Do While Target <> Feuil3.Cells(a, 1)
        If Feuil3.Cells(a, 1) = "" Then
            Exit Sub
       End If
       If Target = Feuil3.Cells(a, 2) Then
           Exit Do
       End If
       a = a + 1
   Loop

  ligne = Target.Row
   colo = Target.Column
   Feuil1.Cells(ligne, colo).Interior.Color = Feuil3.Cells(a, 1).Interior.Color

' protéger la feuille

    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        False, AllowFiltering:=True

End Sub
'

Merci d'avance si vous pouvez me donner la raison de cette erreur et me donner une solution pour faire fonctionner la modification multiple des cellules !

Cordialement,

Onafe57 !

Bonsoir,

Tant que je change la valeur de mes cellules une à une, tout va bien, ça marche, mais lorsque je modifie plusieurs cellules (sélection d'une zone de cellule puis Ctrl+Entrée), j'ai une erreur 13 : Incompatibilité de type, qui apparait. Idem si je supprime le contenu de plusieurs cellules d'un coup.

Rien de plus normal, ton code ne traite qu'une cellule, donc toute modification affectant plusieurs cellules déclenche une erreur.

Tu devrais commencer par :

    If Target.Count > 1 Then Exit Sub

Cela si le traitement simultané de plusieurs cellules ne rentre pas dans le champ d'exécution prévu.

S'il doit y entrer, il faut par contre modifier la macro pour assurer le traitement prévu...

Tu devrais donc mettre un fichier, et donner un peu plus de précisions sur le traitement effectué. On pourra par la même occasion réécrire proprement cette procédure en en extirpant le code enregistré (Select et autres éléments parasites) et en la simplifiant un minimum.

Cordialement.

Merci de ta réponse !

C'est un code que je récupère d'un fichier créé par un autre.

Pas simple, surtout que c'est des macro qui ont été enregistrées par excel lui même ...

Le but est que la modification en masse et le copié collé de cellules fonctionnent.

Je vais voir pour essayer de faire un fichier résumé !

Cordialement,

Onafe57

Bonjour.

J'ai trouvé une solution pour la modification en masse ... mais qui a ses limites.

Voilà le code que j'utilise :

Private Sub Worksheet_Change(ByVal Target As Range)

' retirer la protection de la feuille

ActiveSheet.Unprotect

   a = 50
' Recherche du nombre de lignes et de colonnes
Nombrelignes = ActiveWindow.RangeSelection.Rows.Count
Nombrecolonnes = ActiveWindow.RangeSelection.Columns.Count

' Le code ci dessous permet de définir que si c'est une cellule seule qui a été validée (Compte des lignes et cellules = 1 et 1) alors il faut remonter d'une ligne
' sinon, si c'est une "Range" (plusieurs cellules) qui ont été validés par Ctrl+Entrée, on prend en compte l'ensemble des cellules.
If Nombrelignes = 1 And Nombrecolonnes = 1 Then
    ligne = ActiveWindow.RangeSelection.Row - 1
    colo = ActiveWindow.RangeSelection.Column
Else
    ligne = ActiveWindow.RangeSelection.Row
    colo = ActiveWindow.RangeSelection.Column
End If

' Si la cellule qui est en haut à gauche de la zone sélectionnée est vide, alors mettre un fond blanc, sinon, aller choisir la coloration de la cellule qui correspond
   If Feuil1.Cells(ligne, colo) = "" Then
        Target.Select
        With Selection.Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    Else
        Do Until Feuil1.Cells(ligne, colo) = Feuil3.Cells(a, 1)
            If Feuil3.Cells(a, 1) = "" Then
                Target.Select
                With Selection.Interior
                    .Pattern = xlNone
                    .TintAndShade = 0
                    .PatternTintAndShade = 0
                End With
                Exit Do
            End If
            a = a + 1
        Loop
        Target.Select
        With Selection.Interior
            .Color = Feuil3.Cells(a, 1).Interior.Color
        End With
    End If

    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        False, AllowFiltering:=True

End Sub
'

Les limites sont nombreuses.

Notamment le fait que l'on conditionne la coloration de l'ensemble des cellules à la seule cellule située en haut à gauche ... Ce qui implique qu'on ne peut pas faire de copier coller de plusieurs libellés différents ...

J'aurais aimé savoir s'il est possible de demander à ce que lorsqu'il y'a modification de la feuille (c'est le déclencheur de ma macro), on demande à cette macro d'aller voir chaque cellule du Range sélectionné pour ajuster sa coloration en fonction de sa valeur.

J'ai entraperçu des fonctions comme "For each ...", mais je ne sais si elle peuvent permettre ce type de traitement et comment les mettre en œuvre.

Merci d'avance de votre aide.

Cordialement,

Onafe57.

Un message me ramène sur ce sujet parce que j'ai eu l'occasion d'y faire une remarque il y a quelques temps... !

Mais comment peut-on écrire un tel code-fouillis !

Je te suggère de décrire très exactement ce que tu veux que ta macro fasse et dans quelles conditions, et on pourra t'écrire un code qui fera très exactement ce que tu demandes, en totalité et pas autre chose.

Cordialement.

Bonjour.

Tout d'abord, puis je me permettre de vous demander : Pourquoi code-fouilli ? Attention, je ne cherche pas la polémique, mais étant un programmeur totalement amateur, je creuse cette remarque pour essayer de m'améliorer !

Je pensais avoir un code plutôt clair, avec des alinéas quand je suis dans une fonction (comme if, ou des boucle Do ...) pour hiérarchiser, et j'ai d'ailleurs eu l'occasion de voir des codes absolument imbuvables et incompréhensibles comparés au mien (à mes yeux en tout cas ...).

Quelles méthodes utilisez vous pour clarifier votre code au maximum ? Le but, à mes yeux, étant de le rendre accessible à quelqu'un qui aurait besoin de retoucher ce code sans moi par la suite, pour faire évoluer le programme ... c'est une problématique que je vis régulièrement dans mon travail !

Revenons à ma macro en elle même !

Son but est très simple : le fichier Excel sert à gérer les congés d'agents de plusieurs unités de production.

Les congés sont codifiés de plusieurs manières (C, RP, RU, RQ, ...).

A chaque codification, les chefs d'unités peuvent paramétrer une coloration de leur choix dans une autre feuille du classeur.

La macro est déclenchée dès que quelqu'un modifie le contenu d'une (ou plusieurs) cellule(s) pour codifier un (ou plusieurs) congé(s).

La modification de la feuille intervient de plusieurs manières :

  • Appui sur entrée dans le cas d'une cellule modifiée seule
    Appui sur Ctrl+Entrée dans le cas d'un certain nombre de cellules
    Appui sur une flèche directionnelle du clavier pour accéder à une autre cellule
    Suppression du contenu d'une cellule
    Copier coller d'une série de cellules qui n'ont pas toutes la même codification (cette fonction sert à dupliquer des roulements périodiques)

Lorsque cette modification intervient, deux cas possibles : soit la cellule est vide et il faut la remettre blanche, soit il y'a une codification de congé, et en fonction du code de chaque cellule, il faut aller copier la mise en forme qui a été paramétrée par le chef d'unité dans une autre feuille du classeur, comme stipulé plus haut.

C'est grosso modo ce que j'ai essayé de décrire dans ma macro, avec quelques écueils, comme vous pouvez le voir.

En espérant que mon explication est claire.

Je vous remercie par avance de votre aide !

Cordialement,

Onafe57

Bonsoir,

Pourquoi code-fouilli ?

Une évènementielle de type Change te permet de programmer une action automatique liée à la survenance d'un évènement affectant un objet prédéfini : une procédure liée à l'évènement Change est déclenchée par toute modification affectant une ou plusieurs cellules d'une feuille. L'objet concerné est défini par l'emplacement de la procédure qui ne peut être placée que dans un module spécifique attaché à l'objet et est accessible au niveau du code par le mot-clé Me qui, dans tout module d'objet désigne spécifiquement l'objet concerné. La procédure est lancée en fournissant l'indication de la zone (cellule ou plage) concernée par la modification dans une variable utilisable dans le code : Target.

Ce qui suffit amplement à cerner la modification ayant déclenchée l'exécution, tester s'il y a lieu si elle correspond à une modification recherchée pour l'intervention prévue, opérer l'intervention, sur la zone modifiée ou ailleurs... sans avoir à faire appel à une foultitude d'objets (ActiveSheet, ActiveWindow, Feuil1, Selection...) qui ne font que brouiller la compréhension (quand ils ne génèrent pas d'erreur).

Ça c'est ce que j'appelle du fouillis ! Et comme le reste est constitué d'agrégats de morceau de code enregistré et non épuré, cela ne peut qu'ajouter à la confusion (garder du code enregistré [le mieux étant toujours d'éviter l'enregistreur pour produire du code] non épuré ni sensiblement modifié, c'est qu'en général on ne comprend pas ce qu'il fait...).

J'ajoute, à la lumière de ce que tu dis vouloir faire, colorer des cellules, on ne trouve aucune préparation des couleurs à affecter sous une forme rapidement utilisable et on va péniblement rechercher des correspondances pour faire cette affectation...

Si tu me dis : je mets des couleur en fonction du contenu des cellules modifiées... Je dis OK ! On s'assure que la modification concerne les cellules qu'on surveille, si c'est le cas on les cible, on définit un tableau (ou autre) de correspondances valeurs-couleurs, on les parcourt et on affecte la couleur en fonction de la valeur. Et on fait juste ce qu'on a à faire sans tournicoter...

Maintenant il faut tout de même prendre un peu plus de hauteur et se demander pourquoi on n'opère pas par MFC pour ce type de coloration conditionnelle ?

Cordialement.

Bonjour,

J'ai eu la curiosité de venir relire ta procédure... histoire de voir si l'on ne pourrait pas en tirer, au moins partiellement, les indications utiles pour la réécrire correctement...

Ce n'est pas le cas. On sait juste que les références couleurs commencent en A50 de Feuil3. Une plage délimitée, nommée (éventuellement dynamique) aurait pu offrir un critère sûr et précis... mais ce n'est pas le problème essentiel posé par la proc. : on joue carrément la zone d'intervention à la loterie ! Je prends un exemple simple : je frappe une mention en mettons F3, j'en sors en allant positionner le curseur en I7, ce qui va valider la saisie en F3... ! Je me permets d'avoir quelque doute sur le fait que ta proc. fera ce que tu prévois en pareil cas !

Et ce n'est pas un exemple artificiel pour te sortir un cas où cela ne fonctionnerait pas ! Je pratique fréquemment ainsi, et lorsque je travaille au clavier sans intervention de la souris, il m'arrive (rarement) de valider une saisie par Entrée, mais dans le cas général c'est par une touche de déplacement, soit avec 4 possibilités de sortie différentes !

Tu n'es pas sans savoir que le déplacement de la sélection après validation est une option, qui peut être désactivée (ce que je fais depuis que je travaille sur Excel, 25 ans environ), et qui lorsqu'elle ne l'est pas permet de choisir l'une des 4 directions pour le déplacement. Alors, se baser sur la sélection après validation ne peut offrir aucun garantie que l'on va intervenir à l'emplacement voulu !

Cordialement.

Bonjour MFerrand.

Merci pour toutes ces précisions et ce temps passé à me répondre.

Je découvre le fait de pouvoir annuler le déplacement. Ça peut faciliter l'utilisation, à voir.

Maintenant, j'envisage deux solutions :

- Simplifier le code dont je dispose en utilisant la variable Target pleinement. Mais dans ce cas, il va falloir que j'impose aux utilisateurs, la validation d'une saisie par appui sur la touche Entrée, et non par sélection d'une autre cellule, sinon, en effet, c'est la loterie dans le VBA !

- Utiliser la mise en forme conditionnelle, comme tu en parles, mais la problématique que je rencontre (et qui est la raison pour laquelle je n'ai pas utilisé de mise en forme conditionnelle) est de faciliter la mise à jour des mises en forme par les utilisateurs selon le code qui est renseigné, et éventuellement, de pouvoir renseigner de nouveaux codes si besoin.

Sais tu si l'on peut définir une mise en forme conditionnelle que l'on puisse faire évoluer aussi facilement que ce que j'avais fais initialement, à savoir, définir le format à appliquer à chaque code dans une autre feuille "paramètres" ?

Et si oui, comment le mettre en oeuvre ? Toujours à l'aide de quelques lignes de VBA, ou Excel et ses formules suffisent ils dans ce cas ?

Merci d'avance de ta réponse.

Cordialement.

Bonsoir,

Mais dans ce cas, il va falloir que j'impose aux utilisateurs, la validation d'une saisie par appui sur la touche Entrée, et non par sélection d'une autre cellule

Nullement ! Target est toujours la plage modifiée... et oublie la sélection ! Sélectionner (pour agir ensuite sur la sélection) est toujours une opération parasite en VBA, VBA agit directement là où on lui dit d'agir, sans sélection...

Le cas d'une MFC modifiable par les utilisateurs modifie le problème : c'est la plage de références des couleurs qui est alors à mettre sous procédure Change, laquelle procédure reconstruirait les MFC à partir des éléments de cette plage...

Mais sans support fichier, il est difficile de mettre tous les détails au point...

Cordialement.

Bonjour.

Voilà un résumé du fichier avec uniquement le code qui me préoccupe actuellement.

Je vais voir si j'arrive à trouver les méthodes pour construire des MFC en VBA, mais je suis preneur de votre avis et de propositions si vous avez du temps.

En vous remerciant !

Onafe57

Bonjour,

Voilà une proposition :

Function ClrCode(cod As String) As Long
    Dim i%
    If cod <> "" Then
        With [CodClr]
            For i = 1 To .Rows.Count
                If .Cells(i, 1) = cod Then
                    ClrCode = .Cells(i, 1).Interior.Color
                    Exit Function
                End If
            Next i
        End With
    End If
    ClrCode = xlNone
End Function

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Range
    Me.Unprotect
    For Each c In Target.Cells
        c.Interior.Color = ClrCode(c.Value)
    Next c
    Me.Protect , False, True, False, AllowFiltering:=True
End Sub

Ta plage CodeCouleur est nommée : CodClr (en dynamique sur sur un nombre de lignes limitées de A50 à A100, ce qui devrait largement suffire, tu en as déjà pas mal) [à voir dans le gestionnaire de noms]

Cordialement.

Super !

Ça marche. J'ai juste un peu chercher pour nommer la zone de définition des codes couleurs, mais une fois fait, c'est parfait, ça marche !

Bon par contre, je ne comprends pas tout le code. Va falloir que je me penche dessus pour le comprendre. Je vais encore apprendre des choses moi !

Si je comprend bien, tu as le code déclenché par la modification de la feuille qui permet de modifier chaque cellule inclue dans la variable Target, qui appelle une fonction que tu appelles pour gérer l'aspect coloration des cellules selon les intitulés ?

Il n'y a que le code que tu as écris dans la fonction que j'ai un peu de mal à interpréter.

Merci en tout cas du temps passé et de ton aide précieuse !

Cordialement,

Onafe57

Bonjour,

Attention aux apparences : la procédure Change apparaît simplifiée à l'extrême pour deux raisons :

  • la première est que ta feuille étant protégée, l'utilisateur ne peut en fait intervenir que sur la zone dévolue à la saisie de tes codes congés, il n'y a donc pas à limiter l'exécution de la ligne 6 à 43 et à partir de la colonne 9, il ne peut saisir à l'extérieur de cette zone ; il n'y a que sur les lignes 36 et 37 transformées en bordures qu'il pourrait placer une mention inadéquate, risque limité, sans grave conséquence et qu'on peut négliger, même chose vers la droite, on ne peut limiter car ton tableau est à vocation extensible, mais on peut aussi négliger le fait que l'utilisateur aille saisir là où le tableau n'est pas encore formalisé...
  • la seconde est que la recherche de la couleur à affecter est transférée à une fonction qui la renvoie à partir de la mention portée dans la cellule ; on n'a donc plus qu'à parcourir les cellules impactées pour affecter la couleur par simple appel de la fonction à laquelle on passe le contenu de la cellule.

En contrepartie, la fonction contrairement aux apparences est plus simple parce qu'elle doit simplement se contenter de chercher la mention qu'on lui passe dans une plage et renvoyer la couleur de la cellule trouvée (ou "pas de couleur à affecter" le cas échéant). Rien de caché, elle fait juste tout ce qu'il est logique qu'elle fasse.

On lui facilite le travail en nommant la plage à parcourir. On teste au départ la valeur transmise, car si on lui passe une valeur "vide", inutile qu'elle parcoure la plage avant de renvoyer "no couleur" (on gagne du temps !). Sinon elle parcourt et dès qu'elle a trouvé elle renvoie la couleur...

La couleur est une valeur numérique de type Long. Il ne faut pas te laisser abuser par les expressions compliquées renvoyées par l'enregistreur qui diffèrent selon les endroits où tu as appuyé pour enregistrer... Tout ce code est toujours inutile et peut être utilement supprimé !

Une couleur est TOUJOURS définissable par ....Color = nombre associé à une couleur. Car il n'y a qu'un système de couleurs affichable... Si l'on prend la propriété Color du fond d'une cellule, c'est ce nombre que l'on renvoie directement, lequel peut également être renvoyé par la fonction RGB à partir des 3 composantes (rouge, vert, bleu) de la couleur [qu'il est plus simple d'utiliser dans le cas général pour reproduire une couleur : on se positionne dessus, on ouvre la boîte couleur de remplissage, on va sur l'onglet Personnalisé dans lequel les composantes Rouge Vert Bleu de la couleur sont affichées, et qu'il n'y a plus qu'à insérer dans une fonction RGB pour produire la même couleur.

Cordialement.

Rechercher des sujets similaires à "probleme coloration masse vba"