Activer la ligne de la référence cherchée en VBA Excel

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


Sujets que vous pourriez aussi aimer :


Chercher et trouver au clic

Le VBA est capable de toutes les prouesses et nous l'avons déjà constaté au fil des astuces précédentes. Avec ce nouveau sujet Excel, nous allons voir avec quelle simplicité il est possible d'atteindre au clic, un élément recherché dans une base de données.



Sur l'exemple illustré par la capture, l'utilisateur choisit un code cherché à l'aide d'une liste déroulante. Aussitôt, ce code est repéré en vert dans un tableau des références, placé juste en-dessous. Dès lors, l'utilisateur double clique sur cette référence et bascule automatiquement sur la feuille de la base de données, précisément sur la ligne de ce code. Elle offre tous les renseignements attachés à ce produit.

Classeur Excel à télécharger
Pour monter cette astuce VBA Excel, nous suggérons de récupérer un classeur offrant ce tableau des références et cette base de données.

• Télécharger le classeur trouver-au-clic.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 des deux feuilles de ce classeur. Elle est nommée choixRef. En cellule C3, si vous choisissez une référence avec la liste déroulante, son code est instantanément repéré en couleur dans le tableau du dessous. Désormais et comme nous l'avons expliqué, un double clic sur cette référence repérée doit conduire sur la ligne attachée, dans la base de données de la feuille Catalogue.

La procédure VBA
Au double clic sur un code, c'est une procédure VBA qui doit être exécutée pour trouver la ligne correspondant à la référence demandée. Cette procédure, bien que vide, existe déjà.

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur VBA Excel,
• Dans l'explorateur sur la gauche, déployer le dossier Module,
• Puis, double cliquer sur l'élément Module1,

Sub trouverRef(laRef As String)

End Sub

Nous découvrons effectivement la présence de la procédure vierge à implémenter.

Recherche au double clic
Nous l'avons dit, cette procédure doit être appelée au double clic sur l'une des cellules du tableau des références de la première feuille de ce classeur.

• Dans l'explorateur sur la gauche, double cliquer sur l'élément Feuil1(choixRef),

Nous affichons ainsi la feuille de code VBA associée à la première feuille de ce classeur, 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'élément Worksheet,

Cette action a pour effet de créer la procédure événementielle Worksheet_SelectionChange. Ce n'est pas celle qui nous intéresse. L'événement doit intervenir au double clic sur une cellule de la feuille (Worksheet).

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

C'est ainsi que nous créons la procédure événementielle Worksheet_BeforeDoubleClick. La précédente peut être supprimée.



Les actions que nous allons entreprendre vont se déclencher juste avant la conséquence du double clic qui a pour effet d'activer la modification de la saisie dans la cellule concernée. C'est ainsi que notre code VBA va prendre le pas sur cet événement qui ne verra pas le jour.

Transmettre la valeur à chercher
Vous notez la présence de deux paramètres. Celui qui nous intéresse est nommé Target. Il représente la cellule cliquée. C'est la raison pour laquelle il est typé comme un Range.

• Dans les bornes de la procédure événementielle, ajouter l'appel suivant :

...
trouverRef (Target.Value)
...

Très simplement, nous passons à la procédure trouverRef, le contenu (Value) de la cellule double cliquée par l'utilisateur.

Les variables de la recherche
Maintenant que la main est passée avec la donnée à trouver, nous devons nous soucier de l'implémentation de la procédure trouverRef.

• Dans l'explorateur, double cliquer sur l'élément Module1 pour afficher sa feuille de code,
• Dans les bornes de la procédure, ajouter les instructions VBA suivantes :

...
Dim laColonne As Range: Dim laRecherche As Range

Worksheets("Catalogue").Activate
...

Nous déclarons deux objets de type plages de cellules (As Range). La vocation de la première variable est de définir la colonne de recherche de la référence dans la seconde feuille. La vocation de la seconde variable est de stocker la cellule portant la valeur cherchée pour pouvoir activer sa ligne. Ensuite, nous activons (Activate) la seconde feuille (Catalogue), puisque c'est ici que la recherche doit être entreprise.

La colonne de recherche
Pour lancer la recherche grâce à la méthode Find, nous devons commencer par définir quelle est la colonne des références sur cette seconde feuille. Il s'agit de la colonne B. Nous devons donc initialiser notre première variable objet sur cette colonne.

• A la suite du code, ajouter la ligne VBA suivante :

...
Set laColonne = Columns("B:B")
...

Nous initialisons (Set) la variable laColonne grâce à la collection Columns sur la colonne B de cette seconde feuille.

Lancer la recherche
Grâce à cette colonne désormais connue, nous allons pouvoir y lancer la recherche de la référence double cliquée par l'utilisateur et passée en paramètre de la procédure.

• A la suite du code, ajouter l'instruction VBA suivante :

...
Set laRecherche = laColonne.Find(laRef)
...

Nous enclenchons la recherche (Méthode Find) de la référence double cliquée (laRef) sur la colonne B (laColonne). Si elle est fructueuse, la méthode répond par la cellule trouvée que nous stockons (Set) dans la variable laRecherche.

Tester le résultat de la recherche
Pour tester le résultat de la recherche, nous devons dégainer une instruction conditionnelle. Si la variable laRecherche porte une référence, alors nous pouvons activer sa cellule. Dans le cas contraire, nous conserverons active la première cellule de la feuille.

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

...
If Not laRecherche Is Nothing Then
laRecherche.Activate
Else
Range("A1").Activate
End If
...

Nous engageons le critère sur une double négation. Comprenez : "Si la recherche ne conduit pas à aucun résultat". Dans ces conditions, nous activons la cellule (Activate) du résultat trouvé. Dans le cas contraire, nous rendons le focus à la première cellule de la seconde feuille pour garder l'affichage sur la partie haute.

Décharger les objets
Enfin, comme il est de coutume et comme vous le savez, une fois que les objets déclarés ne sont plus utilisés, nous devons les détruire pour libérer la mémoire.

• Après l'instruction conditionnelle, ajouter les deux lignes VBA suivantes :

...
Set laColonne = Nothing
Set laRecherche = Nothing
...

Pour détruire ces deux objets, nous les réinitialisons (Set) simplement sur la valeur Nothing.

Activer la ligne de la cellule cherchée
Maintenant, nous pouvons tester cette petite application.

• Revenir sur la première feuille Excel (ALT + Tab),
• Avec la liste déroulante, choisir une référence,
• Puis, double cliquer sur la cellule surlignée en vert.

Comme vous pouvez l'apprécier, en même temps qu'une règle de mise en forme conditionnelle prédéfinie se déclenche, le focus est donné à la ligne de la référence double cliquée.



L'utilisateur peut alors y consulter toutes ses informations de détail. Et comme les volets ont préalablement été figés, bien que la ligne soit hors champs à l'origine, vous constatez que la rangée des entêtes reste visible.