Liste déroulante Excel triée sans trous ni doublons

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


Sujets que vous pourriez aussi aimer :


Liste déroulante sans trous ni doublons

Certes, Excel offre des techniques pour créer des listes sans doublons, sans trous et de surcroît triées. Ces techniques ont d'ailleurs été rendues plus simples avec l'avènement d'Office 365 et la sortie de certaines de ses fonctions matricielles. Pourtant ici, nous proposons de résoudre le cas par le code VBA Excel, avec la plus grande simplicité, vous le constaterez.



Sur l'exemple illustré par la capture, nous travaillons à partir d'un tableau déstructuré. Il présente des lignes vides et des opérations réalisées par les commerciaux d'une entreprise. Ceux-ci peuvent intervenir à plusieurs reprises lorsqu'ils réalisent plusieurs ventes.

Au milieu de ces informations, si l'utilisateur inscrit une nouvelle opération pour un nouveau commercial, à validation, vous constatez que son nom est automatiquement intégré dans la liste de valeurs uniques sur la droite du tableau. De plus, il est parfaitement rangé à sa place dans la chronologie alphabétique. Enfin et un peu plus à droite, une liste adaptative se nourrit de ces informations dynamiques pour offrir à l'utilisateur tous les éléments disponibles, sans doublons ni trous et parfaitement ajustés.

Classeur Excel à télécharger
Pour monter cette solution VBA Excel, nous suggérons d'appuyer les travaux sur ce tableau déstructuré qui offre aussi certains atouts.

• Télécharger le classeur liste-valeurs-uniques-triees.xlsm en cliquant sur ce lien,
• Cliquer droit sur le fichier réceptionné,
• En bas du menu contextuel, choisir l'option 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 déverrouillé pour l'ouvrir dans Excel,

Nous retrouvons le tableau des opérations entre les colonnes B et D. Il présente effectivement de nombreux trous. C'est donc sur la droite, en colonne F, que doit être reconstruite cette liste des noms triés, sans doublons et sans vides. Sur la droite encore, en cellule G4, une liste déroulante doit s'adapter à ce contenu importé, pour s'en nourrir. Et tout cela doit se réaliser automatiquement à validation d'une nouvelle saisie. C'est toute la magie du VBA Excel.

La source de la liste déroulante
Nous l'avons évoqué, pour que la liste déroulante se remplisse précisément des informations extraites, sans déborder donc, nous avons travaillé sa source de données avec la fonction Excel Decaler. Nous proposons de le constater.

• Cliquer sur la cellule G4 pour la sélectionner,
• Dans la fenêtre Excel, cliquer sur l'onglet Données pour activer son ruban,
• Dans la section Outils de données du ruban, cliquer sur le bouton Validation des données,

Comme l'indique la zone Source de la boîte de dialogue, c'est bien la fonction Decaler qui est utilisée pour construire la source de données dynamique de cette liste déroulante :

=DECALER($F$4;;; NBVAL($F:$F)-1)

Son point de départ est la cellule F4 de la liste qui sera reconstituée par le code VBA Excel. Les deux arguments suivants du décalage en ligne et du décalage en colonne sont ignorés (;;;). Nous prélevons bien à partir de F4. C'est la hauteur qui nous intéresse. En effet, le nombre de commerciaux peut varier. Pour connaître leur nombre, nous engageons la fonction NbVal sur cette colonne F, afin de compter tous les éléments non vides qui s'y trouvent. Nous lui retranchons une unité (-1) pour ne pas considérer l'étiquette (Qui) en F3. C'est ainsi que nous obtenons la hauteur à considérer pour prélever les éléments et remplir cette liste déroulante.

• Cliquer sur le bouton Annuler de la boîte de dialogue pour revenir sur la feuille Excel,

Vérifier la saisie en VBA
Pour déclencher l'exécution d'un code VBA à chaque fois qu'une nouvelle opération est renseignée dans le tableau à trous, nous devons créer une procédure événementielle capable de détecter cet événement de modification de cellule.

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

Ainsi, nous affichons la feuille de code VBA associée à la feuille Excel, au centre de l'écran,

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

C'est ainsi que nous créons la procédure événementielle Worksheet_SelectionChange. Mais, nous ne souhaitons pas intervenir au changement de sélection sur la feuille mais au changement de valeur dans l'une des cellules de la colonne D.

• Pour cela, en haut de la feuille de code, déployer la liste déroulante de droite,
• Dans les propositions, choisir l'événement associé intitulé Change,

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

Contrôler la cellule modifiée
L'objet Target passé en paramètre de la procédure représente la cellule modifiée. C'est elle qui déclenche l'événement. Mais, nous devons nous assurer qu'il s'agit bien d'une modification entreprise sur la colonne D, celle des commerciaux. Ainsi, nous n'exécuterons pas inutilement le code VBA. Pour cela et nous en avons l'habitude, nous pouvons exploiter la fonction Intersect dans une instruction conditionnelle. Nous devons établir si l'intersection entre cette cellule et une plage de la colonne D, conduit bien à un résultat.

• Dans la procédure, créer l'instruction conditionnelle suivante :

...
If Not Intersect([D4:D500], Target) Is Nothing Then

End If
...

Nous prévoyons large sur cette colonne D (D4:D500) pour considérer l'ajout potentiel de nouveaux commerciaux à la suite du tableau. Si l'intersection de cette plage avec la cellule modifiée n'est pas nulle (Not ... Is Nothing), nous en concluons qu'un commercial a bien été intégré dans une ligne d'opération. Dans ces conditions (Then), nous choisissons de poursuivre le traitement. Le cas échéant et implicitement, il est avorté.

Filtrer et trier les données
Maintenant, c'est la fonctionnalité Excel de filtre avancé que nous proposons de piloter par le code VBA pour extraire les noms des commerciaux sans doublons. Sur une plage de cellules, c'est la méthode AdvancedFilter qui permet d'exercer ces filtres particuliers. Concernant les tris, il s'agit de la méthode Sort.

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

...
[D3:D500].AdvancedFilter xlFilterCopy, , [F3], True
[F4:F500].Sort [F4]
...

Comme le veut cette fonctionnalité (AdvancedFilter), les étiquettes sources (D3) et de destination (F3) doivent avoir strictement le même nom. C'est le cas ici (Qui). C'est ainsi qu'Excel fait la correspondance pour réorganiser les données d'une plage à une autre. Avec le paramètre xlFilterCopy, nous réalisons la copie de la plage D3:D500 sur laquelle la méthode est appliquée. Nous ignorons le deuxième paramètre (,,) car nous n'avons aucun critère à émettre. En troisième argument, nous désignons le point de départ de la copie sur la cellule F3 pour établir la correspondance entre les étiquettes. En quatrième et dernier argument, avec le booléen True, nous demandons de réaliser une extraction sans doublons.

Et comme nous engageons un tri sur la plage résultante (que nous prévoyons large elle aussi : F4:F500) dans la foulée, nous réorganisons ces données uniques extraites dans l'ordre alphabétique croissant. Nous engageons la clé de tri sur la première donnée de cette rangée (F4).

Remarque : pour une solution sécurisée, il conviendrait de vérifier que la cellule saisie n'est pas numérique, car elle ferait planter le tri. Pour cela, il suffit d'exploiter la fonction VBA IsNumeric sur l'objet Target dans une instruction conditionnelle.

• Enregistrer les modifications (CTRL + S) et basculer sur la feuille (ALT + Tab),
• Sur une ligne vide, ajouter un montant en colonne C,
• Enfoncer la touche Tab pour atteindre la cellule voisine en colonne D,
• Dès lors, saisir un nouveau nom et valider par la touche Entrée du clavier,

Comme vous pouvez l'apprécier, il est automatiquement intégré et rangé dans la liste sans doublons en colonne F. Dans le même temps, il est naturellement intégré dans la liste déroulante, grâce à la fonction Decaler, utilisée en source de données. Il en va de même, si vous saisissez des opérations de nouveaux commerciaux. En revanche, si vous enregistrez une nouvelle vente pour un commercial déjà existant, il est ignoré, comme nous le souhaitions.