Vider les anciens choix des listes dépendantes en VBA

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


Sujets que vous pourriez aussi aimer :


Vider les choix des listes dépendantes

Nous avons déjà appris à relier des listes déroulantes entre elles de maintes façons. Mais à chaque occasion, nous avions accepté la présence de certains petits défauts persistants. Bien que les contenus des listes déroulantes dépendantes se réactualisaient au nouveau choix dans la liste parent, les anciennes valeurs des listes dépendantes restaient gravées dans les cellules cibles. Et avec cette nouvelle astuce VBA Excel, nous allons voir comment corriger simplement et dynamiquement cette anomalie.



Sur l'exemple illustré par la capture, l'utilisateur dispose de trois listes déroulantes reliées, sur la droite d'un tableau vide, destiné à réaliser les extractions en conséquence. S'il choisit un département, la liste du dessous s'actualise pour ne proposer que les villes du département. S'il choisit une ville, la liste du dessous s'actualise pour ne proposer que les activités recensées dans cette ville. Dès lors, s'il change de département, les anciens choix de ville et d'activité s'effacent automatiquement et les listes dépendantes ajustent de nouveau leurs contenus.

Classeur Excel à télécharger
Pour la démonstration de cette astuce VBA Excel, nous suggérons d'appuyer l'étude sur un classeur dans lequel trois listes déroulantes sont déjà articulées entre elles.

• Télécharger le classeur vider-valeur-liste-dependante.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é,

Vous découvrez le tableau vide accompagné de trois listes déroulantes sur la droite. Vous pouvez donc réaliser des choix entonnoirs en partant de la première liste, celle des départements, en passant par la deuxième, celle des villes du département et en terminant par la troisième, celle des activités dans la ville du département.

Mais à ce stade, si vous changez de département ou même simplement de ville, bien que les contenus des listes dépendantes s'actualisent, les anciens choix persistent et ils ne sont plus cohérents. Ils doivent être effacés dès lors qu'un changement est opéré dans la première ou deuxième liste.



Même si ce n'est pas le sujet, l'articulation des ces listes de choix a été construite par calculs dans la feuille nommée Inter. Ces calculs exploitent les fonctions Excel Trier, Unique et Filtre pour extraire les données distinctes et leurs dépendances depuis la source de données hébergée par la feuille BDD. Il est important de savoir que ces trois fonctions n'existent que depuis la version 2019 d'Excel.

Bref, c'est ainsi que les listes déroulantes nourrissent leurs contenus à partir de ces trois colonnes de la feuille Inter. Elles exploitent la fonction Decaler, dans l'outil Validation des données, pour ajuster leur hauteur au contenu importé par ces formules.

Code VBA au choix dans une liste
Pour débuter, nous devons commencer par créer la procédure de code capable de détecter les changements de valeurs sur la feuille et plus précisément dans les cellules des listes déroulantes. Et pour cela, VBA Excel offre un événement dédié.

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

Il s'agit de la feuille hébergeant les listes déroulantes. De fait, sa feuille de code s'affiche 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,

Cette action a pour effet de créer la procédure événementielle Worksheet_SelectionChange. Ce n'est pas celle qui nous intéresse. Nous ne souhaitons pas intervenir au changement de sélection sur la feuille mais au changement de valeur dans certaines cellules.

• De fait, déployer la liste déroulante de droite,
• Dans les propositions, choisir l'événement Change,

C'est ainsi que nous créons la procédure événementielle Worksheet_Change. Un paramètre lui est passé en argument. Il se nomme Target. Cet objet représente la cellule concernée par le changement de valeur.

Coordonnées de la cellule cliquée
En revanche, il n'est pas question de déclencher des actions VBA dès qu'une cellule est modifiée. Nous devons optimiser les ressources. Nous devons nous assurer que le changement de valeur intervient dans la cellule de la première liste déroulante (G4) ou de la deuxième (G7). Il est donc question de tester les coordonnées transmises avec une instruction conditionnelle.

• Dans la procédure Worksheet_Change, ajouter l'instruction conditionnelle suivante :

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

ElseIf (Target.Address = "$G$7") Then

End If
...

Grâce à la propriété Address de l'objet Target, nous cherchons tout d'abord à savoir s'il s'agit de la cellule de la première liste déroulante, soit G4. Les dollars doivent être inscrits. Si ce n'est pas elle, alors (ElseIf), nous cherchons à savoir si la valeur a été modifiée dans la deuxième liste déroulante en cellule G7. En effet, le traitement n'est pas tout à fait le même dans les deux cas.

Effacer les anciens choix des listes dépendantes
Au changement de département en cellule G4, les anciens choix des listes déroulantes dépendantes en cellules G7 et G10 doivent être effacés. Lorsque seule la ville est modifiée en cellule G7, c'est l'ancienne activité choisie en cellule G10 qui doit être supprimée.

• Dans la première branche de l'instruction conditionnelle, ajouter les lignes VBA suivantes :

...
Range("G7").Value = ""
Range("G10").Value = ""
...

Grâce à l'objet Range et à sa propriété Value, nous vidons simplement les contenus des cellules attachées aux deux listes dépendantes, lorsque le département a été changé par l'utilisateur.

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

...
Range("G10").Value = ""
...

Cette fois, au changement de ville dans la deuxième liste déroulante, nous nous contentons de vider le contenu de la cellule attachée à la troisième liste déroulante des activités.

• Enregistrer les modifications (CTRL + S) et basculer sur la feuille Excel (ALT + Tab),
• Choisir un département puis une ville et une activité avec les trois listes déroulantes,
• Puis, changer de département à l'aide de la première liste déroulante,

Comme vous pouvez l'appréciez, les valeurs des cellules de la ville et de l'activité ont été effacées. Mais dans le même temps, les deux listes dépendantes ont été rechargées des éléments attachés.

Si vous choisissez une nouvelle ville associée à ce nouveau département, vous pouvez désigner une activité lui appartenant. Et si vous changez de nouveau la ville, cette fois, c'est seulement la cellule de l'activité qui s'efface tandis que sa liste déroulante se recompose en conséquence.