Choisir la colonne de tri avec une liste déroulante

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


Sujets que vous pourriez aussi aimer :


Changer la vue d'un tableau

Avec cette nouvelle astuce VBA Excel, nous allons apprendre à changer instantanément la vue d'un tableau au choix d'une référence dans une liste déroulante.



Sur l'exemple illustré par la capture, l'utilisateur travaille sur une base de données des activités de sorties. Sur la droite, une liste déroulante lui propose de désigner l'une de ses colonnes pour réorganiser automatiquement ses informations. Par exemple, s'il choisit la colonne de la ville, toutes les activités sont triées dans l'ordre croissant, par rapport à cette dernière, donc de la première à la dernière ville.

Ce mécanisme est intéressant pour étudier les données sous différents angles tout en évitant de surcharger l'espace avec les filtres automatiques que propose Excel.

Classeur Excel à télécharger
Nous suggérons de baser l'étude sur un classeur Excel qui offre cette base de données dense, donc propice aux réorganisations à la volée.

• Télécharger le classeur choix-colonne-tri-vba.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 l'unique feuille de ce classeur. Elle est nommée valeursUniquesTriees. La base de données des activités de sorties s'étend entre les colonnes B et E, débute à partir de la ligne 3 et se répand jusqu'à la ligne 1000. Sur la droite, plus précisément en cellule G4, une liste déroulante permet de désigner l'une des colonnes de cette base, par son nom d'entête. Bien sûr à ce stade, elle n'a aucun impact sur le tableau.

Plage dynamique
Pour que cette réorganisation puisse se faire en intégrant les potentielles données insérées à la volée, nous avons travaillé un nom de plage dynamique pour ce tableau, notamment grâce à la fonction Excel Decaler. Nous l'exploiterons dans le code VBA pour désigner la base potentiellement évolutive. Nous proposons de constater sa présence et de consulter sa syntaxe.

• En haut de la fenêtre Excel, cliquer sur l'onglet Formules pour activer son ruban,
• Dans la section Noms définis du ruban, cliquer sur le bouton Gestionnaire de noms,

Vous notez la présence d'une plage nommée Tabl avec la syntaxe suivante :

=DECALER(valeursUniquesTriees!$B$3:$E$3; ; ; NBVAL(valeursUniquesTriees!$B:$B) - 1)

Grâce à la fonction Decaler, nous la faisons débuter à partir de la ligne d'entête du tableau ($B$3:$E$3). Nous ne touchons ni au deuxième, ni au troisième argument (;;;). Ils permettent respectivement de définir le décalage à observer en ligne et en colonne. Nous n'avons aucun décalage à réaliser. Par contre, en quatrième argument de la fonction Decaler, nous réglons sa hauteur dynamique en fonction des éléments présents en colonne B et ce, grâce à la fonction NbVal. C'est ainsi que cette plage peut évoluer en hauteur. Et c'est ainsi que nous devrions pouvoir considérer les nouvelles entrées dans les tris. Nous le verrons.

Au choix dans la liste
Un code VBA doit donc être déclenché au changement de valeur dans la liste déroulante. Mais comme cette liste n'est pas un contrôle ActiveX, elle n'offre pas de propriétés et encore moins d'événements. En revanche, nous pouvons détecter les changements opérés dans les cellules de la feuille. Et si la cellule concernée est bien la cellule G4, nous saurons qu'il s'agit d'un choix de colonne effectué dans la liste déroulante. Nous le savons bien, c'est l'événement Change associé à la feuille active que nous devons gérer.

• Fermer le gestionnaire de noms en cliquant sur le bouton Fermer,
• 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 Feuil1(valeursUniquesTriees),

Ainsi, nous affichons au centre de l'écran, la feuille de code VBA associée à la première feuille du classeur. Elle est encore vierge pour l'instant.

• En haut de la feuille de code, déployer la liste déroulante de gauche,
• Puis, cliquer sur l'élément Worksheet,

Cette action a pour effet de créer la procédure événementielle Worksheet_SelectionChange. Mais nous l'avons dit, nous souhaitons intervenir au changement de valeur dans une cellule et non pas au changement de sélection sur la feuille.

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

Cette fois, nous créons la procédure Worksheet_Change. C'est bien celle qui nous intéresse. En conséquence, la précédente peut être supprimée.

La variable et les coordonnées
Maintenant, nous avons besoin de déclarer une variable dont le rôle sera de mémoriser la position de la colonne choisie, dans la ligne d'entête du tableau. C'est grâce à elle que nous pourrons définir la clé de tri pour réorganiser les informations du tableau. De plus, il n'est pas utile de déclencher ce code VBA à chaque fois qu'une cellule est modifiée sur la feuille. Seule la cellule G4 de la liste déroulante est concernée. Donc, nous devons observer un test.

• Dans les bornes de la procédure événementielle, ajouter les instructions VBA suivantes :

...
Dim col As Byte

If Target.Address = "$G$4" Then

End If
...

Nous déclarons la variable col comme un entier court (As Byte). Cette base de données n'est constituée que de quelques colonnes. Ensuite, nous exploitons l'objet Target passé en paramètre de la procédure. Il représente la cellule cliquée. C'est la raison pour laquelle, dans une instruction conditionnelle, nous exploitons sa propriété Address pour vérifier que la cellule impliquée est bien celle de la liste déroulante (G4). Les dollars sont importants dans la comparaison avec cette propriété.

La position de la colonne choisie
Lorsque le traitement est autorisé, nous devons procéder à la recherche de la valeur choisie avec la liste déroulante, dans la ligne d'entêtes du tableau (B3:E3). Pour cela, nous pouvons piloter la fonction Equiv par le code VBA. Mais attention, en anglais elle se nomme Match.

• Dans l'instruction conditionnelle, ajouter la ligne VBA suivante :

...
col = WorksheetFunction.Match(Target.Value, [B3:E3], 0) - 1
...

Grâce à l'objet WorksheetFunction, nous appelons la fonction Match (Equiv). Nous lui demandons de chercher la colonne choisie avec la liste déroulante (Target.Value), dans la ligne d'entête du tableau ([B3:E3]) avec une correspondance exacte (0). Nous retranchons une unité à ce résultat (-1) car il s'agit d'un décalage à opérer pour la clé du tri, à partir de la cellule B3 qui sera déjà incluse dans le décompte. Désormais, la position de la colonne à utiliser comme clé de tri est mémorisée dans la variable col.

Tri et clé de tri
Nous avions déjà appris à réorganiser un tableau Excel en définissant dynamiquement la colonne de tri. Mais pour l'instant, nous ne connaissons que sa position. L'astuce consiste à la désigner explicitement comme clé, en réalisant un décalage par rapport à la colonne B3, grâce à la fonction Decaler (Offset en anglais). Nous le verrons, ici, il s'agit en fait d'une méthode à appliquer sur une cellule de référence, le premier entête.

• A la suite dans l'instruction conditionnelle, ajouter les lignes VBA suivantes :

...
Worksheets("valeursUniquesTriees").Sort.SortFields.Clear

With Worksheets("valeursUniquesTriees").Sort
.SetRange [tabl]
.SortFields.Add Key:=[B3].Offset(0, col)
.Header = xlYes
.Apply
End With
...

Tout d'abord, nous supprimons le potentiel précédent tri enregistré en agissant sur les champs triés (SortFields) de la feuille valeursUniquesTriees. Ensuite, nous engageons un bloc With pour ne pas avoir à répéter l'objet parent qui désigne la feuille et sa méthode Sort, pour débuter la définition du tri. Puis, nous exploitons la méthode enfant SetRange pour définir le tableau à trier, reconnu par la plage dynamique tabl. Dès lors, nous définissons la colonne de tri, grâce à la méthode Add de la collection SortFields. Pour cela, dans l'argument Key, nous appliquons la méthode Offset (Decaler) sur la première cellule de la ligne d'entête. Grâce à la variable col, nous lui demandons de rejoindre l'entête de la colonne choisie (décalage à l'horizontale). En réglant la propriété Header sur la valeur xlYes, nous demandons de ne pas inclure les entêtes dans la réorganisation. Enfin, nous appliquons (Apply) ce tri selon ces préférences.

Le code est terminé et il est très simple pour un résultat remarquable. Nous proposons de le constater.

• Enregistrer les modifications (CTRL + S) et revenir sur la feuille Excel (ALT + Tab),
• Puis, changer plusieurs fois de clé de tri avec la liste déroulante en cellule G4,

Comme vous pouvez l'apprécier, la base de données est réorganisée à chaque occasion en respectant l'organisation alphabétique sur la rangée désignée comme colonne de tri.

Et si vous ajoutiez de nouvelles activités à la suite du tableau, vous constateriez qu'elles seraient automatiquement intégrées dans la réorganisation, grâce à la plage de hauteur variable (Tabl).