Extraire les données de la valeur cherchée en commentaire

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


Sujets que vous pourriez aussi aimer :


Extraire en commentaires

Avec cette nouvelle astuce VBA Excel, nous allons apprendre à extraire automatiquement les informations attachées à une donnée cherchée, pour les placer en commentaires de la cellule en question. L'idée, dans le cas de bases de données déjà denses, est de ne pas imposer les informations complémentaires, mais de les suggérer.



Sur l'exemple illustré par la capture, à partir d'une feuille de facturation par exemple, l'utilisateur déploie une liste déroulante pour choisir une référence. A validation, la cellule de la référence sélectionnée est parée d'un commentaire dans lequel toutes les informations attachées à la donnée cherchée, sont rapatriées. Ces informations sont issues d'une autre feuille du classeur dans laquelle la base de données est hébergée.

Classeur Excel à télécharger
Pour développer cette nouvelle solution VBA Excel, nous suggérons d'appuyer les travaux sur un classeur hébergeant cette base de données vestimentaire.

• Télécharger le fichier extraire-en-commentaires.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ébouchons sur la première feuille de ce classeur. Des listes déroulantes sont disponibles en colonne C pour choisir une référence.



Ces références sont inscrites dans la seconde feuille de ce classeur, nommée Bdd, avec toutes les informations attachées, qu'il s'agira de rapatrier automatiquement en commentaires, au changement de valeur dans l'une des cellules de la colonne C de la première feuille.

Code VBA au changement
Pour que ces importations très spéciales de données puissent se produire au choix d'une valeur dans l'une des listes déroulantes de la colonne C depuis la première feuille, un code VBA Excel doit automatiquement être déclenché au changement de valeur. Pour cela, l'éditeur propose une panoplie d'événements à implémenter.

• 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(Extraction),

Ainsi, nous affichons la feuille de code VBA associée à la feuille des listes déroulantes, au centre de l'écran.

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

De cette manière, nous créons la procédure événementielle Worksheet_SelectionChange. Mais ce n'est pas celle qui nous intéresse. Le code VBA doit intervenir non pas au changement de sélection, mais au changement de valeur dans l'une des cellules de la feuille.

• Déployer alors la liste déroulante de droite,
• Dans les propositions, choisir l'événement associé Change,

Cette fois, nous obtenons la bonne procédure : Worksheet_Change.

Les variables
Des variables sont nécessaires pour assembler les informations correspondantes, pour trouver la position de la référence choisie dans la base de données et pour parcourir toutes les colonnes de cette dernière.

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

...
Dim chaine As String: Dim pos As Byte: Dim i As Byte
...

Nous déclarons la première variable comme un texte (As String) pour assembler les données textuelles trouvées et les deux suivantes comme des entiers courts (As Byte) fort naturellement.

Economiser les ressources
Pour économiser les ressources, ce code VBA ne doit pas être déclenché à tout va. Il doit intervenir seulement lorsqu'une valeur a été changée dans la troisième colonne de la première feuille, la colonne C. Nous devons donc tester l'indice de colonne de la cellule modifiée. Dans cette procédure événementielle, c'est l'objet Target qui récupère en paramètre l'information de la cellule impliquée.

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

...
If Target.Column = 3 And Target.Count = 1 Then

End If
...

Nous vérifions qu'il s'agit bien de la colonne C (.Column = 3) et dans le même temps, qu'une seule cellule a été modifiée (.Count = 1).

Position de la référence dans la BDD
Pour trouver la position de la référence choisie dans la base de données, le VBA peut faire appel à des fonctions de feuille de calcul. C'est ce que nous entendons entreprendre en imbriquant les fonctions Equiv (Match en anglais) et Index (Idem en anglais).

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

...
Target.ClearComments
pos = Application.Match(Target.Value, Application.Index([bdd], , 1), 0)
...

Tout d'abord, nous exploitons la méthode ClearComments de l'objet Target. De cette manière, nous effaçons son précédent commentaire, dû à un précédent potentiel choix de référence avec la liste déroulante. Puis, grâce à l'objet Application, nous lançons la fonction de calcul Equiv (Match ici). Notez que nous aurions pu utiliser l'objet WorksheetFunctions à la place de l'objet Application. En premier argument, nous lui passons la référence cherchée (Target.Value). En guise de tableau de recherche et grâce à la fonction Index, nous lui fournissons la première colonne de la base de données, reconnue sous le nom de plage bdd (Application.Index([bdd], , 1)). Enfin, nous demandons une correspondance exacte avec le dernier paramètre de la fonction Equiv (0).

Récolter les données attachées
Maintenant que la position de la référence choisie est connue dans la base de données, nous pouvons parcourir les colonnes de la ligne, relativement à la plage Bdd de la deuxième à la sixième, pour assembler les données attachées qu'il s'agira ensuite de greffer en commentaire. Nous avons donc besoin d'une boucle. C'est la raison pour laquelle nous avons déclaré la variable i.

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

...
For i = 2 To 6
chaine = chaine & [titres].Cells(1, i) & " : " & [bdd].Cells(pos, i) & vbLf
Next i
...

Nous parcourons donc les colonnes relatives à la base (Bdd) de la C à la G (2 To 6). Nous le savons désormais, la base de données est nommée bdd. La ligne des entêtes est nommée titres. Vous pouvez le constater en déployant la zone Nom en haut à gauche de la feuille Excel. Pour chaque colonne de la référence, aux renseignements précédents déjà récoltés, nous associons (chaine &) le titre de la colonne en cours ([titres].Cells(1, i)) avec son information repérée sur la ligne pos ([bdd].Cells(pos,i)), puis nous passons à la ligne (vbLf), pour accueillir les données complémentaires à l'occasion du prochain passage dans la boucle.

Inscrire les données en commentaire
Maintenant que les informations sont récoltées et assemblées, il ne nous reste plus qu'à les afficher en commentaire dans la cellule de la référence choisie avec la liste déroulante.

• A la suite dans l'instruction conditionnelle, ajouter la ligne VBA suivante :

...
Target.AddCommentThreaded chaine
...

Sur la cellule active, nous ajoutons un commentaire grâce à la méthode AddCommentThreaded appliquée sur l'objet Target. Bien sûr, nous lui passons les informations à afficher, assemblées dans la variable chaine.

Le code VBA est terminé. Nous pouvons donc le tester.

• Enregistrer les modifications (CTRL + S) et basculer sur la feuille Excel (ALT + Tab),

Désormais et comme vous pouvez l'apprécier, à chaque fois que vous choisissez une référence avec une liste déroulante en colonne C, cette dernière s'affiche avec un commentaire dans le coin supérieur droit. Si vous pointez dessus avec la souris, vous avez le plaisir de constater que toutes les informations attachées à la référence choisie sont effectivement suggérées sous forme de commentaire surgissant à la demande.