Couleurs sur la ligne et la colonne de la cellule cliquée

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


Sujets que vous pourriez aussi aimer :


Ligne et colonne de la cellule cliquée

Avec cette nouvelle astuce VBA Excel, il est question de voir comment il est possible de surligner en couleur la ligne et la colonne complètes de la cellule cliquée dans un tableau. L'intérêt est simple. Il s'agit de mettre en évidence les informations à débattre lors de présentations.



Sur l'exemple illustré par la capture, lorsque l'utilisateur clique sur une cellule précise d'un tableau, la sélection est étendue jusqu'aux bornes de ce dernier, aussi bien en largeur qu'en hauteur, pour faire ressortir en jaune l'intégralité de la ligne et de la colonne concernées.

Classeur Excel à télécharger
Pour la découverte de cette trouvaille, nous suggérons d'appuyer l'étude sur un classeur abritant un tableau de données assez volumineux.

• Télécharger le classeur ligne-colonne-selection.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 découvrons le tableau fidèle à celui de la présentation. Il archive des activités de sorties catégorisées notamment par départements.

Code VBA au changement de sélection
Le premier enjeu consiste à faire réagir les cellules du tableau au clic de l'utilisateur dans une cellule. VBA Excel offre des gestionnaires d'événements. L'un d'entre eux détecte le changement de sélection, soit ces fameux clics dans ces cellules de coordonnées différentes. C'est ainsi qu'il peut déclencher l'exécution d'un code VBA au gré des actions de l'utilisateur sur la feuille. Pour débuter, nous proposons de créer cette procédure événementielle.

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

Ainsi, nous affichons au centre de l'écran, la feuille de code VBA associée aux données du tableau Excel.

• En haut de la feuille de code, déployer la liste déroulante de gauche,
• Dans les propositions, choisir l'élément Worksheet,

Cet élément désigne une feuille, en l'occurrence ici, la feuille 1 nommée Naviguer. Et c'est ainsi que nous créons la procédure événementielle Worksheet_SelectionChange. Son code se déclenchera à chaque changement de sélection, donc à chaque clic dans une nouvelle cellule. Vous notez qu'un élément lui est passé en paramètre. Il se nomme Target et il est de type Range (Plage ou cellule). Il représente la cellule cliquée. Grâce à lui, nous allons pouvoir connaître ses coordonnées.

La déclaration des variables
Pour surligner la ligne et la colonne entières de la cellule désignée, nous avons besoin de connaître la ligne et la colonne de la cellule active mais aussi les lignes et colonnes des cellules situées aux extrémités, aussi bien à l'horizontale qu'à la verticale. Et pour cela, nous devons commencer par déclarer les variables destinées à recevoir ces informations.

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

...
Dim ligneActive As Integer: Dim colonneActive As Integer
Dim ligneDeb As Byte: Dim ligneFin As Integer
Dim colonneDeb As Byte: Dim colonneFin As Byte
...

Nous déclarons six variables comme des entiers, parfois en entiers courts pour les indices de colonne et la première ligne du tableau et en entiers longs pour les indices de ligne et aussi pour la ligne active.

Code VBA au clic dans le tableau seulement
Maintenant et avant de songer à affecter ces variables, nous devons penser à optimiser les ressources. Ce code ne doit pas être déclenché inutilement. Il doit réagir si et seulement si la cellule cliquée appartient bien au tableau de données. Celui-ci est nommé bdd. Vous pouvez le constater en déployant la zone Nom en haut à gauche de la feuille Excel.

• Après les variables, donc à la suite du code, ajouter l'instruction conditionnelle suivante :

...
If Not Intersect(Target, [bdd]) Is Nothing Then

End If
...

La fonction Intersect porte bien son nom. Nous lui passons deux plages en paramètres, celle de la cellule cliquée et celle du tableau reconnu par son nom (bdd). Elle indique simplement s'il existe une ou des cellules à l'intersection, soit portant les mêmes coordonnées. Grâce à la double négation (Not et Is Nothing), nous nous assurons que cette intersection existe bien, en d'autres termes que la cellule cliquée appartient bien au tableau. C'est seulement à cette condition que nous pouvons poursuivre le traitement, d'où la nécessité d'une instruction conditionnelle.

Réinitialiser les couleurs du tableau
Poursuivons ! Comme nous allons modifier la couleur de la ligne et de la colonne pour la cellule cliquée, avant chaque nouveau clic, nous devons commencer par restituer les couleurs d'origine. Le plus simple est d'agir sur la plage complète (bdd) du tableau, sans se soucier des cellules précisément à réattribuer.

• Dans l'instruction conditionnelle, ajouter les deux lignes VBA suivantes :

...
[bdd].Interior.Color = Range("A1").Interior.Color
[bdd].Font.Color = Range("A1").Font.Color
...

Nous descendons simplement jusqu'à la propriété enfant Color pour le fond (Interior) et le texte (Font) de chaque cellule du tableau. Et nous leur attribuons les réglages d'usine en prélevant arbitrairement ceux de la cellule A1.

Ligne et colonne de la cellule cliquée
Il est temps de prélever les indices de ligne et de colonne de la cellule cliquée. Pour cela, l'objet Target passé en paramètre de la procédure événementielle offre les propriétés dédiées.

• A la suite du code, toujours dans le If, ajouter les deux instructions VBA suivantes :

...
ligneActive = Target.Row
colonneActive = Target.Column
...

Ce sont les propriétés Row et Column qui renvoient respectivement les numéros de ligne et de colonne pour la cellule cliquée. Nous les stockons dans les variables ligneActive et colonneActive.

Lignes et colonnes aux extrémités
A partir de ce point de départ, nous devons étendre la sélection pour obtenir les indices des cellules en début et fin de ligne mais aussi en début et fin de colonne. Pour cela, il suffit d'exploiter la propriété End que nous avons découverte récemment. Elle permet de pointer sur une extrémité à désigner par rapport à un point de départ.

• A la suite du code et toujours dans le If, ajouter les instructions VBA suivantes :

...
colonneDeb = Target.End(xlToLeft).Column
colonneFin = Target.End(xlToRight).Column
ligneDeb = Target.End(xlUp).Row + 1
ligneFin = Target.End(xlDown).Row
...

Pour la colonne de départ (colonneDeb), nous passons le paramètre xlToLeft à la propriété End de l'objet Target. Nous décelons ainsi la première colonne du tableau dont nous prélevons l'indice avec la propriété Column. De la même façon, nous décelons la colonne de fin (colonneFin) grâce au paramètre xlToRight. Ce sont ensuite et respectivement les valeurs xlUp et xlDown qui donnent les indices de ligne aux extrémités (ligneDeb et ligneFin). Notez néanmoins que nous ajoutons une unité à la ligne de départ (+1) pour ignorer la ligne de titre.

Surligner la ligne et la colonne
Maintenant que toutes les coordonnées sont connues, nous allons pouvoir exploiter l'objet Range pour délimiter les plages définissant la ligne et la colonne dont il s'agit de changer de couleur.

• Toujours à la suite du code et dans le If, ajouter les quatre instructions VBA suivantes :

...
Range(Cells(ligneActive, colonneDeb), Cells(ligneActive, colonneFin)).Interior.Color = vbYellow
Range(Cells(ligneDeb, colonneActive), Cells(ligneFin, colonneActive)).Interior.Color = vbYellow
Range(Cells(ligneActive, colonneDeb), Cells(ligneActive, colonneFin)).Font.Color = vbBlack
Range(Cells(ligneDeb, colonneActive), Cells(ligneFin, colonneActive)).Font.Color = vbBlack
...

Nous définissons tout d'abord la couleur de fond pour la ligne active qui part de la première cellule du tableau (Cells(ligneActive, colonneDeb)) et s'étend jusqu'à la dernière (Cells(ligneActive, colonneFin)). Cette couleur est un jaune (vbYellow). Nous faisons de même pour la colonne active. Puis, pour ces deux même rangées, nous forçons la couleur du texte (Font.Color) sur du noir (vbBlack).

• Enregistrer les modifications (CTRL + S) et basculer sur la feuille Excel (ALT + Tab),
• Dès lors, cliquer à l'intérieur du tableau,

Comme vous pouvez le voir, la ligne et la colonne complètes de la cellule sélectionnée, sont instantanément mises en valeur.

Gérer les exceptions
Cependant des anomalies existent et nous devons les gérer. Si vous cliquez sur la dernière colonne du tableau (Colonne F), le débogueur se déclenche.



Comme nous sommes déjà placés sur la dernière colonne du tableau, la méthode de déplacement ne parvient pas à la déceler.

De même et après avoir réinitialisé le programme avec le bouton Stop, si vous cliquez sur une cellule de la première colonne, la sélection déborde pour considérer la cellule située juste avant le tableau.



Un autre souci survient également en désignant une cellule de la dernière ligne du tableau. Bref, ce sont les cellules aux extrémités qui sont incriminées. Pour corriger ces dysfonctionnements, il convient d'adapter le programme comme suit (Les nouvelles lignes sont en gras) :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ligneActive As Integer: Dim colonneActive As Integer
Dim ligneDeb As Byte: Dim ligneFin As Integer
Dim colonneDeb As Byte: Dim colonneFin As Byte

colonneFin = 0: ligneFin = 0

If Not Intersect(Target, [bdd]) Is Nothing Then
[bdd].Interior.Color = Range("A1").Interior.Color
[bdd].Font.Color = Range("A1").Font.Color

ligneActive = Target.Row
colonneActive = Target.Column

colonneDeb = Target.End(xlToLeft).Column
If (Cells(ligneActive, colonneDeb).Value = "") Then
colonneDeb = colonneActive
End If
On Error GoTo gererColonne
colonneFin = Target.End(xlToRight).Column
gererColonne:
If (colonneFin = 0) Then colonneFin = colonneActive
ligneDeb = Target.End(xlUp).Row + 1
On Error GoTo gererLigne
ligneFin = Target.End(xlDown).Row
gererLigne:
If (ligneFin = 0) Then ligneFin = ligneActive

Range(Cells(ligneActive, colonneDeb), Cells(ligneActive,colonneFin)).Interior.Color = vbYellow
Range(Cells(ligneDeb, colonneActive), Cells(ligneFin, colonneActive)).Interior.Color = vbYellow
Range(Cells(ligneActive, colonneDeb), Cells(ligneActive, colonneFin)).Font.Color = vbBlack
Range(Cells(ligneDeb, colonneActive), Cells(ligneFin,colonneActive)).Font.Color = vbBlack
End If

End Sub

Nous initialisons d'abord les variables de fin pour voir si elles évoluent (colonneFin = 0:ligneFin = 0). Si la cellule pour la colonne de départ est vide (If(Cells(ligneActive, colonneDeb).Value = "")), nous en concluons que l'utilisateur a cliqué dans la première colonne du tableau et que le programme a retenu la cellule vide d'avant. Donc, nous réinitialisons la colonne de départ sur la colonne active (colonneDeb = colonneActive), soit la première du tableau. Si une erreur est générée lors de l'affectation de la colonne de fin, nous déclenchons un gestionnaire d'erreur (On Error GoTo gererColonne) qui renvoie à l'étiquette gererColonne. Cette étiquette intervient après la tentative d'affectation. Dans ces conditions, nous réaffectons la colonne de fin sur la colonne active (gererColonne:If (colonneFin = 0) Then colonneFin = colonneActive), soit la dernière du tableau. De la même façon, lorsqu'une erreur est rencontrée lors de l'affectation de la ligne de fin (On Error GoTo gererLigne), nous la réaffectons sur la ligne active, soit sur la dernière ligne du tableau.

Désormais, quel que soit l'endroit sur lequel vous cliquez dans le tableau, ce sont bien la ligne et la colonne complètes qui sont mises en surbrillance, sans que plus aucune erreur ne soit générée.