Aspect des cellules selon les mots des commentaires

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


Sujets que vous pourriez aussi aimer :


Mots clés et commentaires

A l'occasion de l'astuce VBA Excel précédente, nous avons vu comment faire ressortir en couleurs, les cellules de la feuille portant soit des notes, soit des commentaires. Pour cela, nous avons créé deux fonctions booléennes en VBA, que nous avons exploitées dans deux règles de mise en forme conditionnelle.



Ici, l'objectif est plus précis et c'est ce qu'illustre le résultat finalisé présenté par la capture. Il est question de faire ressortir en couleurs les cellules des notes et des commentaires, si et seulement si ces derniers portent au moins l'un des mots clés d'une liste personnalisée. C'est la raison pour laquelle, certaines cases pourtant accompagnées de notes ou de commentaires, arborent une apparence totalement classique, sans mise en forme.

Classeur Excel à télécharger
Comme lors du volet précédent, nous suggérons d'appuyer les travaux sur ce classeur hébergeant ce calendrier déjà travaillé.

• Télécharger le classeur aspect-mots-commentaires.xlsm en cliquant sur ce lien,
• Cliquer droit sur le fichier réceptionné,
• En bas du menu contextuel, choisir la commande Propriétés,
• En bas de la boîte de dialogue, cocher la case Débloquer et valider par Ok,
• Puis, double cliquer sur le fichier du classeur pour l'ouvrir dans Excel,

Nous retrouvons le calendrier construit sur tous les mois de l'année. Les jours de Week-End ressortent en bleu. Les notes et commentaires sont marqués d'une encoche, en haut à droite de la case, en rouge et en violet. Mais à ce stade bien sûr, aucun de ces commentaires ne ressort en couleur.

La construction des fonctions
Nous devons donc créer deux fonctions booléennes pour savoir si les cellules testées, en plus qu'elles portent des notes ou commentaires, contiennent l'un des mots clés spécifiés par une liste personnalisée. Ces fonctions doivent être signées avec un objet en paramètre. Il s'agit de celui représentant la cellule à tester.

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur VBA Excel,
• Depuis l'explorateur de projet sur la gauche, double cliquer sur l'élément Module1,

Nous l'avions créé par anticipation (Insertion / Module). Ainsi, nous affichons sa feuille de code, vierge pour l'instant, au centre de l'écran.

• Dans cette feuille de code, créer les deux fonctions VBA suivantes :

...
Function motCleCom(cellule As Range) As Boolean

End Function

Function motCleNote(cellule As Range) As Boolean

End Function
...

Comme nous l'avons annoncé, nous les typons comme des fonctions booléennes puisque leur vocation est de retourner le résultat d'un test logique.

Les variables
Nous proposons de commencer par développer la fonction analysant les commentaires. Nous pourrons facilement décliner le principe sur la fonction analysant les notes. Nous avons tout d'abord besoin de variables notamment pour stocker les mots clés à analyser mais aussi pour les parcourir.

• Dans les bornes de la fonction motCleCom, ajouter les déclarations suivantes :

...
Dim lesMots As String: Dim tabMots
Dim com As String: Dim i As Byte
...

Dans la variable lesMots, nous stockerons les mots clés à trouver en les séparant par des espaces. Puis, nous les rangerons dans le tableau de variables tabMots, grâce à la fonction Split. Nous parcourrons ce tableau avec la variable i, que nous ferons varier du premier au dernier élément. Nous exploiterons la variable com, pour récupérer le texte du commentaire de la cellule, si elle en propose un.

Les mots clés
Maintenant que les variables sont déclarées, nous pouvons nous soucier des mots clés à dénicher.

• A la suite du code de la fonction, ajouter les instructions VBA suivantes :

...
Application.Volatile
lesMots = "Mission Anniversaire"
tabMots = Split(lesMots, " ")
...

Tout d'abord et comme vous le savez, la méthode Volatile de l'objet Application permet de forcer le recalcul automatique de la fonction, dès qu'une modification est détectée sur la feuille Excel. Ensuite, nous énumérons les mots clés dans la variable lesMots. Cette technique permet de les faire évoluer à souhait. Puis, nous les rangeons indépendamment dans le tableau de variables tabMots, grâce à la fonction Split qui les fractionne sur les espaces de séparation.

La présence d'un commentaire
Maintenant, nous allons dégainer la même astuce que lors du volet précédent pour tester si la cellule passée en paramètre de la fonction, héberge bien un commentaire.

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

...
If Not cellule.CommentThreaded Is Nothing Then
com = cellule.CommentThreaded.Text

End If
...

Nous l'avions vu, c'est l'objet enfant CommentThreaded appliqué sur l'objet parent cellule qui représente un commentaire. S'il existe bien (Not Is Nothing), alors nous stockons son texte dans la variable com.

Remarque : Il aurait été plus judicieux de procéder à l'affectation des variables précédentes dans les bornes de cette instruction. En l'absence de commentaire en effet, le code VBA sera avorté et ces affectations auront été produites pour rien.

Parcourir tous les mots clés
Si le commentaire existe, nous devons partir à la recherche de chaque mot clé dans son texte. Et pour cela, nous devons les parcourir tour à tour, grâce à une boucle.

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

...
If Not cellule.CommentThreaded Is Nothing Then
com = cellule.CommentThreaded.Text
For i = LBound(tabMots) To UBound(tabMots)
If InStr(com, tabMots(i)) > 0 Then
motCleCom = True
End If
Next i
End If
...

Les fonctions LBound et UBound renseignent respectivement sur la borne inférieure et la borne supérieure du tableau de variables intitulé tabMots. Grâce à elles, nous engageons la variable i dans une boucle pour parcourir tous ses éléments. A chaque passage, c'est alors la fonction InStr qui permet de tester la présence du mot clé en cours d'analyse (tabMots(i)) dans le commentaire (com). En effet, dès que cette fonction répond par une valeur positive (>0), elle indique qu'elle a trouvé l'élément cherché. Dans ces conditions, nous réglons la valeur de retour de la fonction sur le booléen True (motCleCom = True). C'est ainsi que nous pourrons l'exploiter dans une règle de mise en forme pour faire ressortir les cellules des commentaires certes, mais seulement dans la mesure où ils renferment au moins l'un des mots clés souhaités.

Tester les notes
Nous l'avons évoqué, pour tester les notes, le procédé est similaire. Mais ce n'est plus l'objet enfant CommentThreaded que nous devons utiliser. C'est tout simplement l'objet enfant Comment qui représente une note.

• Copier et adapter le code précédent dans la fonction motCleNote, comme suit :

Function motCleNote(cellule As Range) As Boolean
Dim lesMots As String: Dim tabMots
Dim com As String: Dim i As Byte

Application.Volatile
lesMots = "Mission Anniversaire"
tabMots = Split(lesMots, " ")

If Not cellule.Comment Is Nothing Then
com = cellule.Comment.Text
For i = LBound(tabMots) To UBound(tabMots)
If InStr(com, tabMots(i)) > 0 Then
motCleNote = True
End If
Next i
End If
End Function

Repérer les commentaires avec mots clés
Il ne nous reste plus qu'à mettre en place ces fonctions dans des règles de mise en forme conditionnelle, pour faire ressortir en couleurs les cellules des notes et des commentaires, dans la mesure où elles hébergent au moins l'un des mots clés cherchés.

• Basculer sur la feuille Excel (ALT + Tab),
• Sélectionner toutes les cellules du calendrier, soit la plage B4:M34,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas des propositions, choisir l'option Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour ...,
• Cliquer dans la zone de saisie du dessous pour l'activer,
• Dès lors, construire la syntaxe suivante : =motCleCom(B4),

Nous l'avons rappelé dans le volet précédent, l'analyse d'une mise en forme conditionnelle est chronologique par rapport à la plage sélectionnée. C'est pourquoi, nous faisons débuter l'étude à partir de la première d'entre elles (B4). Elle ne doit surtout pas être figée (pas de dollar) pour que l'analyse se déplace au fur et à mesure sur les autres cellules. Si cette fonction répond favorablement, nous savons que la cellule en cours porte un commentaire avec au moins l'un des mots clés. Dans ce cas, nous devons la faire ressortir en couleur.

• Pour cela, cliquer sur le bouton Format en bas de la boîte de dialogue,
• Dans celle qui suit, activer l'onglet Remplissage,
• Dans la palette de couleurs, choisir un jaune orangé,
• Activer alors l'onglet Police de la boîte de dialogue,
• Cliquer sur le style gras puis déployer la liste déroulante du dessous,
• Choisir un jaune foncé pour la couleur du texte et valider par Ok,

Nous sommes ainsi de retour sur la première boîte de dialogue qui résume parfaitement l'apparence que revêtiront les cellules des commentaires abritant les mots clés cherchés. Et si vous validez la création de cette règle par le bouton Ok, vous constatez effectivement que quelques cellules avec commentaires surgissent dans ces couleurs, mais pas toutes. Celles n'hébergeant aucun mot de la liste, demeurent vierges de mise en forme. Il s'agit donc d'un très bon procédé pour résumer un tableau dense et faire ressortir les éléments importants, sans devoir pointer sur chaque commentaire pour faire surgir leurs contenus à analyser. Nous en avons désormais une idée par avance.

Pour les notes, le principe est identique. Sur la même plage de cellules présélectionnée, il s'agit de bâtir la règle suivante : =motCleNote(B4) et de l'associer à des jeux de couleurs dans des tons de vert par exemple.

A validation et là encore, seules les notes proposant au moins l'un des mots clés sont repérées. En revanche et comme nous l'avons expliqué dans le cas du volet précédent, la modification ou l'ajout d'un commentaire avec mots clés, n'applique pas instantanément la couleur à la cellule. Il n'y a que la modification du contenu des cellules qui induit le recalcul des formules de la feuille. Mais si vous enfoncez la touche F9 du clavier, vous forcez le recalcul et toutes les couleurs s'actualisent.