Couleurs automatiques des lignes pour compter les absences

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Densité de couleur et absences

Dans les pas du développement précédent, nous souhaitons mettre le VBA Excel à contribution pour gérer un planning rendant instantanément compte de l'assiduité des salariés et des collaborateurs.



Sur l'exemple illustré par la capture, les absences des salariés à des stages sont marquées par des croix (x). Au lieu de stages, il pourrait bien sûr s'agir des jours d'un mois de travail. En fonction de la répétition des absences pour un même salarié, la densité de couleur appliquée n'est pas la même. Elle dépend d'un barème, placé sur la droite, dans une colonne intitulée Notation. A chaque nouvelle inscription d'une croix, c'est un code VBA Excel qui se déclenche automatiquement, pour compter les absences et récupérer la couleur correspondante dans le barème, par recherche approchante. Plus la couleur de la ligne tire vers le rouge, plus le verdict visuel est parlant. L'opérateur sait a priori à qui il a à faire.

Comme pour le cas précédent, cette solution VBA est beaucoup plus avantageuse que des règles de mise en forme conditionnelle. Elle permet de considérer facilement de nombreux autres paliers. De plus, sans le VBA, il faudrait bâtir autant de règles de mise en forme conditionnelle qu'il y a de tranches d'absences à considérer.

Classeur Excel à télécharger
Pour développer cette nouvelle solution, nous suggérons d'appuyer l'étude sur un classeur offrant cette grille et ce barème.

• Télécharger le classeur couleurs-lignes-absences.xlsm en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous retrouvons bien la grille des absences au centre de l'écran. Les couleurs sont déjà appliquées. Nous les ferons varier lorsque le code VBA sera en place pour considérer le tarif coloré à appliquer en fonction du nombre de croix inscrites correspondant aux indications fournies en colonne O.

Si vous déployez la zone Nom en haut à gauche de la feuille Excel, vous constatez que les cellules de la grille des absences sont reconnues sous l'intitulé absences. La plage des barèmes colorés est quant à elle reconnue sous l'intitulé notations.

La procédure et les variables
Nous devons commencer par déclarer les variables qui nous permettront de piloter les plages de cellules et de les questionner.

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur VBA Excel,
• Dans l'explorateur sur la gauche, double cliquer sur l'élément Feuil1(couleursAbsences),
• En haut de la feuille de code, déployer la liste déroulante de gauche,
• Dans les propositions, choisir l'objet Worksheet,
• Déployer alors la liste déroulante de droite,
• Dans les propositions, choisir l'événement Change,

C'est ainsi que nous créons la procédure événementielle Worksheet_Change. Son code est destiné à se déclencher dès qu'une modification est opérée dans la feuille Excel.

• Dans les bornes de cette procédure, ajouter les déclarations suivantes :

...
Dim i As Byte: Dim nb As Byte: Dim pos As Byte
Dim tabCoul As Range: Dim listNotes As Range
...

Nous typons les trois premières variables comme des entiers courts (Byte). Nous les utiliserons respectivement pour parcourir les lignes du tableau, compter les croix et trouver le barème correspondant dans la plage intitulée notations. Nous typons les deux variables suivantes comme des plages de cellules (Range). Nous les utiliserons pour représenter celles de la feuille Excel.

Coordonnées de la cellule modifiée
La suite du traitement ne doit être entreprise que si nous avons la certitude que la cellule modifiée appartient bien à la plage des absences. Pour cela, une astuce efficace existe et nous l'avons présentée à l'occasion des volets précédents. Elle concerne la méthode Intersect de l'objet Application.

• A la suite du code, créer l'instruction conditionnelle suivante :

...
If Not Application.Intersect([absences], Target) Is Nothing Then
Set tabCoul = [absences]: Set listNotes = [notations]

End If
...

Nous testons le croisement de la plage des absences ([absences]) avec la ou les cellules modifiées sur la feuille. C'est l'objet Target, passé en paramètre de la procédure qui la, ou les représente. Ensuite, nous initialisons les deux objets Range sur les deux plages nommées de la feuille.

Parcourir les lignes du tableau
Maintenant, ce sont toutes les lignes du tableau que nous devons parcourir, pour réactualiser les couleurs en fonction de la ou des modifications effectuées. En effet, rien n'empêche l'utilisateur d'influer dans une colonne, sur plusieurs lignes à la fois pour inscrire des croix. Nous devons donc engager une boucle partant de la première ligne pour rejoindre la dernière.

• A la suite du code de l'instruction conditionnelle, créer la boucle For Next suivante :

...
For i = 1 To tabCoul.Rows.Count

Next i
...

tabCoul est l'objet qui représente la plage des absences. La propriété Rows est la collection de ses lignes qui permet d'atteindre la propriété enfant Count. Vous l'avez compris, cette dernière renvoie le nombre de lignes de cette plage que nous parcourons grâce à cette boucle.

Compter et repérer les absences
Pour chacune de ces lignes, nous devons maintenant compter le nombre de croix et repérer le palier concordant dans la plage nommée notations. Nous devons dégainer les fonctions Nb.Si et Equiv. Mais en anglais, donc en VBA, elles ne portent pas ces noms. Elles se nomment CountIf et Match. C'est ainsi que nous devons les appeler par le code.

• Dans la boucle, ajouter les deux lignes VBA suivantes :

...
nb = Application.CountIf(Application.Index(tabCoul, i, 0), "x")
pos = Application.Match(nb, listNotes, 1)
...

Nous enclenchons donc la fonction Nb.Si (CountIf), sur la ligne en cours d'analyse (i) renvoyée par la fonction Index, appliquée sur le tableau des absences (tabCoul). Le critère de dénombrement est celui de la croix (x). Le décompte est retourné, pour chaque ligne analysée, dans la variable nb. Sur ce décompte, nous enclenchons la fonction Equiv (Match) pour trouver la valeur la plus proche, directement inférieure (1), dans la plage notations (listNotes). Cette position est enregistrée dans la variable pos.

Répercuter les densités de couleurs
Si la fonction Equiv (Match) ne répond pas par une erreur, nous savons qu'une concordance a été trouvée avec un palier dans la grille intitulée notations. Nous devons alors pointer sur cette position pour récupérer les couleurs et les répliquer sur la ligne en cours d'analyse dans la plage intitulée absences.

• Toujours à la suite du code de la boucle, créer l'instruction conditionnelle suivante :

...
If Not IsError(pos) Then
With Application.Index(tabCoul, i, 0)
.Interior.Color = listNotes(pos).Interior.Color
.Font.Color = listNotes(pos).Font.Color
End With
End If
...

Si une position a bien été trouvée (If Not IsError(pos) Then), pour la ligne en cours dans la plage des absences (With Application.Index(tabCoul, i, 0)), nous prélevons les attributs de couleur de la grille de notation pour les répliquer sur cette ligne du tableau des absences. Pour cela et nous avions découvert cette astuce à l'occasion du volet précédent, nous pointons sur la ligne de la plage en la mentionnant entre parenthèses.

Désormais, après avoir enregistré, au gré des croix que vous ajoutez ou supprimez dans le tableau des absences, vous constatez que les couleurs varient en fonction des paliers atteints. C'est ainsi que l'administrateur peut accéder à une synthèse visuelle instantanée.



Si vous souhaitez influer sur plusieurs cases à la fois, il suffit de les sélectionner ensemble, de saisir une croix et de la valider par le raccourci CTRL + Entrée pour répandre cette dernière sur toutes les cellules sélectionnées. C'est alors le code VBA qui prend le relai pour analyser chaque ligne du planning des absences pour réajuster les densités de couleur en fonction des absences constatées, en corrélation avec la grille de notation.