Listes déroulantes liées en cascade 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 :


Listes déroulantes reliées en cascades

Excel propose la fonction Indirect qui permet de relier assez simplement des listes déroulantes entre elles. Mais elle suppose que les données soient organisées de façon spécifique et impose que les plages de valeurs dépendantes soit nommées selon le choix réalisé dans la liste parent. Finalement, lorsqu'il s'agit de relier facilement des listes déroulantes entre elles, le VBA Excel est beaucoup plus puissant.



La capture ci-dessus illustre l'application finalisée où les listes déroulantes reliées entre elles en cascades, proposent des choix restreints selon ceux effectués en amont dans les listes parents. Elles sont alimentées depuis une base de données des idées de sorties en France. Chaque idée est référencée dans un département, une ville et une activité précise. Ainsi l'internaute choisit le département pour lequel il souhaite trouver des idées. Puis, il affine sa recherche en précisant l'activité souhaitée, grâce à la deuxième liste déroulante. Dès lors, il peut encore affiner la recherche jusqu'à la ville avec la troisième liste déroulante. Les listes déroulantes sont bien sûr reliées entre elles. Les activités proposées dans la deuxième dépendent du choix du département effectué dans la première. Les villes suggérées dans la troisième liste déroulante dépendent des deux premiers choix.

Base de données source
Dans cette application, nous nous contenterons de mettre en oeuvre le code VBA Excel capable de relier les listes déroulantes entre elles. Dans une prochaine formation, nous nous soucierons d'extraire les résultats correspondant aux choix recoupés. Pour charger les listes déroulantes de contenu, nous avons besoin d'une source de données. C'est la raison pour laquelle nous proposons de débuter les travaux depuis un classeur existant.

• Télécharger le fichier listes-cascade-vba.xlsm en cliquant sur son lien,
• L'ouvrir dans Excel puis cliquer sur le bouton Activer la modification du bandeau de sécurité,

L'extension xlsm de ce fichier prévoit naturellement d'accueillir le code VBA de notre développement. Ce classeur est constitué de trois feuilles : bd_sorties, listes_cascade et construction. La première accueille la base de données des idées de sorties à manipuler. La deuxième propose l'interface utilisateur déjà montée. Les listes déroulantes ont été paramétrées. Il s'agit de contrôles activeX permettant l'implémentation des événements. En effet au changement de valeur, un code VBA doit être déclenché pour charger les listes suivantes. La dernière feuille sert de traitements intermédiaires. Les données y seront isolées pour être purgées des doublons avant d'être chargées dans les listes déroulantes.

• Cliquer sur l'onglet listes_cascade en bas de la fenêtre Excel pour afficher sa feuille,
• Cliquer sur l'onglet Développeur en haut de la fenêtre Excel pour afficher son ruban,

Si l'onglet Développeur n'est pas disponible dans votre environnement, la formation pour débuter la programmation en VBA Excel rappelle comment l'activer.

• Dans la section Contrôles du ruban, cliquer sur le bouton Mode création,
• Sélectionner alors la première liste déroulante de la feuille,
• Puis, dans la section Contrôles du ruban, cliquer sur le bouton Propriétés,

Nous affichons ainsi la fenêtre des propriétés du contrôle ActiveX sélectionné sur la feuille Excel. Comme vous le constatez, cette première liste déroulante se nomme liste_dep (propriété Name). Les deux autres dans l'ordre se nomment liste_act et liste_villes.

• Cliquer sur la croix de la fenêtre Propriétés pour la fermer,
• Puis cliquer sur le bouton Mode Création du ruban Développeur,

Nous revenons ainsi en mode exécution permettant d'actionner les listes déroulantes pour les dérouler. Cependant à ce stade, elles sont vides.

Charger une liste déroulante purgée des doublons
Le premier Combobox liste_dep doit proposer l'ensemble des départements recensés dans la base de données de la feuille bd_sorties, en colonne D. Etant donné que de nombreuses activités sont présentes dans le même département, ces derniers sont répétés de très nombreuses fois.

Cette liste doit proposer un contenu dès l'ouverture du classeur. Nous devons donc écrire un code dans l'événement Workbook_Open, comme nous l'avait appris la formation pour importer et exporter des données en VBA Excel. Ce code doit récupérer l'ensemble des départements de la feuille bd_sorties pour les inscrire dans la colonne B de la feuille construction. Il devra alors se charger d'éliminer les doublons et de trier les résultats pour proposer une organisation satisfaisante dans la liste déroulante.

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur de code VBA Excel,
• Dans l'explorateur de Projet sur la gauche, double cliquer sur l'élément ThisWorkbook,

ThisWorkbook est l'objet Visual Basic Excel qui désigne le classeur en cours. Cette action permet ainsi d'afficher sa feuille de code, vierge pour l'instant, au centre de l'écran.

• Actionner la première liste déroulante au-dessus de la feuille de code,
• Dans les choix proposés, choisir l'objet Workbook,

Nous créons ainsi la procédure capable d'intercepter l'événement de l'ouverture du classeur. Tout code VBA saisi entre ses bornes se déclenchera donc au démarrage. Nous devons commencer par déclarer les variables nécessaires. Nous avons besoin de variables pour parcourir les lignes et colonnes des feuilles ainsi que d'une variable pour mémoriser la plage de cellules traitée.

• Entre les bornes de la procédure, ajouter les déclarations de variables suivantes :

Dim colonne As Integer: Dim ligne As Integer
Dim ligne_bd As Integer: Dim plage As Range

Hormis la variable plage déclarée comme un objet de type Range pour manipuler les plages de données, toutes les autres variables sont déclarées comme des entiers. Deux d'entre elles sont prévues pour parcourir les lignes, sur deux feuilles différentes.

Avant de débuter le traitement consistant à récupérer les départements, nous devons réinitialiser certaines zones. Tout d'abord les listes déroulantes doivent être réinitialisées. Dans la feuille construction, les colonnes B, D et F doivent être vidées de leur contenu à partir de la ligne 5. Un changement de département impose une réactualisation de toutes les données.

• A la suite du code, ajouter les instructions suivantes :

Sheets('listes_cascade').liste_dep.Clear
Sheets('listes_cascade').liste_act.Clear
Sheets('listes_cascade').liste_villes.Clear
Sheets('listes_cascade').liste_dep.Value = ''
Sheets('listes_cascade').liste_act.Value = ''
Sheets('listes_cascade').liste_villes.Value = ''

Grâce à la méthode Clear d'un objet Combobox, nous vidons tour à tour les listes déroulantes de leur contenu potentiel. Pour y accéder, nous exploitons l'objet VBA Sheets qui permet de désigner leur feuille, dont le nom est passé en paramètre. Puis, grâce à la propriété Value du Combobox, nous supprimons le potentiel dernier choix toujours inscrit par défaut (Value = '').

• Puis à la suite du code, ajouter la boucle de traitement suivante :

For colonne = 2 To 6 Step 2
ligne = 5
While Sheets('construction').Cells(ligne, colonne).Value <> ''
Sheets('construction').Cells(ligne, colonne).Value = ''
ligne = ligne + 1
Wend
Next colonne

La boucle For permet de faire varier la variable colonne de deux en deux grâce à son pas (Step). Ainsi en partant de deux, nous passons en revue les colonnes 2, 4 et 6 soit B, D et F. Nous initialisons la variable ligne à 5 pour chaque nouvelle colonne traitée afin de ne pas altérer les cellules de référence situées en lignes 3 et 4 de la feuille construction. Puis une boucle While permet de passer en revue toutes les cellules non vides de chaque colonne. Chaque cellule incrémentée est désignée grâce à l'objet Cells avec les indices de ligne et de colonne en paramètre. Sa propriété Value permet d'accéder à son contenu. S'il n'est pas vide (Value <> ''), alors nous le supprimons (Value = ''). Nous n'oublions pas d'incrémenter la variable ligne après chaque traitement (ligne = ligne + 1) pour atteindre la suivante de façon récursive. Lorsque le critère de la boucle While (Value <> '') n'est plus vérifié, son traitement s'arrête naturellement. Comme elle est encapsulée dans une boucle For, le code reprend à la prochaine colonne tant que la dernière colonne (6) n'est pas atteinte.

Comme nous n'avons pas encore de contenu, nous ne pouvons pas tester ce code. Donc nous proposons d'ajouter le traitement capable de récupérer tous les départements de la feuille bd_sorties. Une boucle While doit parcourir la colonne D de cette dernière. Tant que son contenu n'est pas vide, le département ainsi réceptionné doit être inscrit à la suite, en colonne B de la feuille construction, en partant de la ligne 5.

• Donc, à la suite du code, ajouter la boucle de traitement suivante :

ligne_bd = 2: ligne = 5
While Sheets('bd_sorties').Cells(ligne_bd, 4).Value <> ''
Sheets('construction').Cells(ligne, 2).Value = Sheets('bd_sorties').Cells(ligne_bd, 4).Value
ligne = ligne + 1: ligne_bd = ligne_bd + 1
Wend

L'énumération des départements sur la feuille bd_sorties commence en ligne 2 tandis que les cellules prévues pour les accueillir dans la feuille construction, commencent en ligne 5, d'où la double affectation de départ (ligne_bd = 2: ligne = 5). La boucle While permet alors de débuter la lecture en colonne D (Cells(ligne_bd, 4)), tant que le contenu n'est pas vide (Value <> ''). Chaque département prélevé est ainsi inscrit dans la colonne B de la feuille construction (Sheets('construction').Cells(ligne,2).Value =). Après chaque traitement, les variables sont incrémentées pour déplacer les pointeurs de lecture sur les lignes du dessous.

• Enregistrer les modifications (CTRL + S) et exécuter la procédure (F5),
• Basculer sur le classeur Excel (ALT + F11) et afficher la feuille construction,

Comme vous le constatez et comme l'illustre la capture ci-dessous, tous les départements de la source de données ont été répliqués sur la feuille construction, dans l'ordre où ils étaient inscrits. Il s'agit désormais de traiter cette liste indépendante pour la purger de ses doublons. Ainsi nous n'affecterons pas la source de la base de données.

Nous pourrions développer un code permettant de supprimer les lignes de doublons, comme nous l'avions fait dans la formation VBA Excel pour purger des tableaux.



Mais ici nous préférons réaliser une macro automatique permettant de supprimer les doublons puis de trier les données restantes. Ainsi, nous n'aurons plus qu'à récupérer le code VBA résultant et à l'adapter. Nous allons donc devoir simuler ces actions pendant l'enregistrement, comme nous l'avait appris la formation Excel sur les macros. Il est important de commencer par changer de feuille.

• Cliquer sur l'onglet listes_cascade en bas de la fenêtre Excel pour activer sa feuille,
• Cliquer sur l'onglet Développeur en haut de la fenêtre Excel pour activer son ruban,
• Dans la section Code tout à fait à gauche, cliquer sur le bouton Enregistrer une macro,
• Dans la boîte de dialogue qui suit, la nommer purger,
• Puis cliquer sur Ok pour débuter l'enregistrement,
• Cliquer sur l'onglet construction en bas de la fenêtre Excel pour activer sa feuille,
• Sélectionner les cellules des départements répliqués, soit la plage B5:B1001,
• Cliquer sur l'onglet Données en haut de la fenêtre Excel pour activer son ruban,
• Dans la section Outils de données du ruban, cliquer sur le bouton Supprimer les doublons,
• Dans la boîte de dialogue qui suit, décocher la case Mes données ont des en-têtes,
• Puis cliquer sur Ok pour enclencher la suppression des doublons,
• Confirmer le message qui suit indiquant qu'il ne reste plus que 20 départements uniques,
• Cliquer sur l'onglet Accueil en haut de la fenêtre Excel pour activer son ruban,
• Dans la section Edition tout à fait à droite, cliquer sur le bouton Trier et filtrer,
• Dans la liste, choisir Trier de A à Z,
• Cliquer sur l'onglet Développeur en haut de la fenêtre Excel pour activer son ruban,
• Dans la section Code, cliquer sur le bouton Arrêter l'enregistrement,

La macro existe désormais. Toutes les actions que nous avons simulées ont été transcrites en code Visual Basic Excel. Ainsi nous saurons supprimer les doublons et trier une plage de données en VBA. Vous avez noté qu'il n'a pas été nécessaire de redéfinir la plage de cellules à trier. La fonction de tri d'Excel est capable d'ignorer les cellules vides, suite à la suppression des valeurs en double. Ces instructions ont été écrites dans une procédure indépendante d'un module. C'est ce que nous allons vérifier.

• Basculer dans l'éditeur de code VBA Excel (ALT + F11),
• Dans l'explorateur de projet, cliquer sur le symbole + du dossier Modules pour déployer son affichage,
• Double cliquer alors sur le module qui apparaît pour afficher son code,

Les instructions VBA de la procédure purger apparaissent dans la feuille de code. Certaines lignes ne nous intéressent pas. Elles concernent des actions de défilement dans la feuille notamment. C'est ce qu'illustre la propriété ScrollRow de l'objet ActiveWindow répliqué à maintes reprises.



Les lignes que nous devons prélever pour les adapter sont illustrées en surbrillance sur la capture ci-dessus.

• Sélectionner les lignes de code suivantes et les copier (CTRL + C) :

ActiveSheet.Range('$B$5:$B$1001').RemoveDuplicates Columns:=1, Header:=xlNo
ActiveWindow.SmallScroll Down:=-3
ActiveWorkbook.Worksheets('construction').Sort.SortFields.Clear
ActiveWorkbook.Worksheets('construction').Sort.SortFields.AddKey:=Range('B5'), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets('construction').Sort
.SetRange Range('B5:B1001')
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

• Dans l'explorateur de projet, cliquer sur l'élément ThisWorkbook pour revenir dans le code de la procédure de démarrage,
• Avant le End Sub, coller (CTRL + V) ces lignes,

Ces instructions ne peuvent pas être exploitées en l'état. Tout d'abord, les plages de cellules sont censées être dynamiques. Elles doivent donc être adaptées par le code. Les feuilles sur lesquelles les opérations sont produites doivent être désignées de façon explicite. L'objet ActiveSheet doit être remplacé.

• Avant les lignes copiées, soit après le Wend de la dernière boucle, ajouter les instructions suivantes :

ligne = ligne - 1
Sheets('construction').Select
Set plage = Range(Cells(5, 2), Cells(ligne, 2))
plage.Select

Nous décrémentons tout d'abord la variable ligne pour recaler son indice sur la dernière cellule non vide qu'avait parcouru la boucle. Puis nous activons explicitement la feuille construction, grâce à la méthode Select de l'objet VBA Sheets. Nous initialisons et affectons notre objet plage sur la plage de cellules commençant à la ligne 5 jusqu'à la dernière ligne enregistrée de la colonne B (Set plage = Range(Cells(5, 2), Cells(ligne, 2))). Puis, nous sélectionnons cette plage.

• Sur la ligne suivante précédemment copiée, remplacer la plage fixe (B5:B1001) par la plage dynamique, comme suit :

ActiveSheet.Range(Cells(5, 2), Cells(ligne, 2)).RemoveDuplicates Columns:=1, Header:=xlNo

Notre objet Range ne peut pas être exploité avec la méthode RemoveDuplicates, sous peine de générer une erreur. C'est la raison pour laquelle, nous lui repassons la plage définie avec des bornes variables grâce à l'objet Cells. La méthode RemoveDuplicates permet comme vous l'avez compris, de supprimer toutes les occurrences trouvées en double dans la plage définie. Elle réalise finalement le même traitement que celui que nous avions développé dans la formation pour purger les tableaux.

Les lignes suivantes concernent le tri pour afficher les départements dans l'ordre croissant dans la liste déroulante. Nous pourrions simplifier la syntaxe mais nous proposons de nous concentrer simplement sur l'adaptation. Les deux premières instructions peuvent être conservées telles quelles :

ActiveWorkbook.Worksheets('construction').Sort.SortFields.Clear
ActiveWorkbook.Worksheets('construction').Sort.SortFields.Add Key:=Range('B5'), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

La première ligne permet de vider la mémoire d'un précédent potentiel tri stocké. La seconde initialise les réglages du tri en indiquant notamment la première cellule du tableau (Range('B5')) et l'ordre croissant (Order:=xlAscending).

Le bloc With qui suit consiste à définir les bornes de la plage à trier et à savoir s'il faut considérer des en-têtes notamment.

• Dans la méthode SetRange de l'énumération, remplacer la plage de cellules fixe par l'objet plage, comme suit :

With ActiveWorkbook.Worksheets('construction').Sort
.SetRange plage
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Désormais le tri s'effectue sur une plage de cellules aux bornes dynamiques. Le code est adapté et fonctionnel. Si vous l'exécutez, vous obtiendrez le même résultat que celui offert par la macro. Avant de tester le bon déroulement du code, il nous reste à charger la liste déroulante des départements avec ce contenu purgé et trié.

• A la suite du code, après le End With, ajouter les instructions suivantes :

ligne = 4
While Sheets('construction').Cells(ligne, 2).Value <> ''
Sheets('listes_cascade').liste_dep.AddItem Sheets('construction').Cells(ligne, 2).Value
ligne = ligne + 1
Wend

Les éléments de la liste doivent être chargés un à un à l'aide d'une boucle qui les parcourt tous. C'est la raison pour laquelle nous réaffectons la variable ligne sur la valeur 4 afin d'intégrer cette fois le titre : Sélectionner un département. La boucle While parcourt alors toutes les cellules non vides de la colonne B dans la feuille construction. A chaque passage, la méthode AddItem du ComboBox liste_dep permet d'ajouter le contenu de la cellule en cours de lecture, soit le département unique. Comme toujours, nous n'oublions pas d'incrémenter la variable ligne à chaque passage, afin de déplacer le pointeur de lecture.

Pour parfaire le fonctionnement de notre code de démarrage, il convient de réafficher la feuille listes_cascade et de sélectionner automatiquement la première valeur de la liste déroulante.

• Pour ce faire, à la suite du code, ajouter les instructions suivantes :

Sheets('listes_cascade').Select
Sheets('listes_cascade').liste_dep.ListIndex = 0

C'est la propriété ListIndex d'un objet ComboBox VBA qui permet de définir ou récupérer l'indice de sélection dans la liste déroulante.

• Enregistrer les modifications et basculer sur le classeur Excel,

Comme vous le remarquez, la feuille listes_cascade est effectivement active par défaut. La liste déroulante semble chargée de contenu comme en atteste l'indication qu'elle affiche par défaut. Si vous cliquez dessus pour déployer son contenu, vous remarquez que tous les départements triés croissants sont en effet présents. C'est ce qu'illustre la capture ci-dessus. Le code complet de la procédure Workbook_Open est le suivant :

Private Sub Workbook_Open()
Dim colonne As Integer: Dim ligne As Integer
Dim ligne_bd As Integer: Dim plage As Range

Sheets('listes_cascade').liste_dep.Clear
Sheets('listes_cascade').liste_act.Clear
Sheets('listes_cascade').liste_villes.Clear
Sheets('listes_cascade').liste_dep.Value = ''
Sheets('listes_cascade').liste_act.Value = ''
Sheets('listes_cascade').liste_villes.Value = ''

For colonne = 2 To 6 Step 2
ligne = 5
While Sheets('construction').Cells(ligne, colonne).Value <> ''
Sheets('construction').Cells(ligne, colonne).Value = ''
ligne = ligne + 1
Wend
Next colonne

ligne_bd = 2: ligne = 5
While Sheets('bd_sorties').Cells(ligne_bd, 4).Value <> ''
Sheets('construction').Cells(ligne, 2).Value = Sheets('bd_sorties').Cells(ligne_bd, 4).Value
ligne = ligne + 1: ligne_bd = ligne_bd + 1
Wend

ligne = ligne - 1
Sheets('construction').Select
Set plage = Range(Cells(5, 2), Cells(ligne, 2))
plage.Select

ActiveSheet.Range(Cells(5, 2), Cells(ligne, 2)).RemoveDuplicates Columns:=1, Header:=xlNo

ActiveWorkbook.Worksheets('construction').Sort.SortFields.Clear
ActiveWorkbook.Worksheets('construction').Sort.SortFields.AddKey := Range('B5'), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets('construction').Sort
.SetRange plage
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

ligne = 4
While Sheets('construction').Cells(ligne, 2).Value <> ''
Sheets('listes_cascade').liste_dep.AddItem Sheets('construction').Cells(ligne, 2).Value
ligne = ligne + 1
Wend

Sheets('listes_cascade').Select
Sheets('listes_cascade').liste_dep.ListIndex = 0

End Sub

Remplir le contenu d'une liste déroulante liée
Désormais, au choix d'un département depuis la première liste déroulante, la seconde liste, celle des activités, doit se remplir en conséquence. Cela signifie que nous devons être en mesure de ne proposer que les activités recensées dans la base de données pour le département sélectionné. Nous produirons ainsi des listes déroulantes en cascade. Nous pourrions par la même occasion en profiter pour remplir les villes dans la troisième liste déroulante. Mais pour simplifier la problématique, nous repoussons ce point que nous traiterons dans la prochaine formation. Elle consistera à réaliser l'extraction des données selon les choix réalisés en cascade.

Avant de générer l'événement du clic sur la liste déroulante, nous devons prévoir la procédure de traitement qui sera appelée au changement de valeur. Cette procédure aura pour mission de traiter les activités correspondant au département choisi.

• Réaliser le raccourci ALT + F11 pour revenir dans l'éditeur de code VBA,
• En haut de l'éditeur, cliquer sur le menu Insertion,
• Dans la liste, choisir Module,
• Dans sa feuille de code au centre, créer la procédure charger_activite, comme suit :

Sub charger_activite()

End Sub

• Afficher de nouveau la feuille listes_cascade du classeur,
• Dans le ruban Développeur, cliquer sur le bouton Mode création,
• Double cliquer alors sur la liste déroulante liste_dep des départements,

Comme vous le remarquez, nous sommes de retour dans l'éditeur de code mais cette fois entre les bornes de la procédure événementielle : liste_dep_Change. Son code se déclenchera donc au changement de valeur détecté dans la liste déroulante. Cette procédure est attachée à la feuille listes_cascade de son objet ComboBox, comme en atteste l'explorateur de projet de la capture ci-dessus.

Nous devons écrire un code capable d'appeler la procédure précédemment créée (charger_activite), dans la mesure où la valeur sélectionnée n'est pas le choix par défaut (Sélectionner un département). Il s'agit donc de vérifier ce critère à l'aide d'une instruction conditionnelle en VBA. Et puisqu'un changement de département est synonyme de réinitialisation, nous devrons purger les autres listes déroulantes ainsi que la zone de critères sur la droite de la feuille listes_cascade.

• Entre les bornes de la procédure liste_dep_Change, ajouter les instructions suivantes :

liste_act.Clear
liste_villes.Clear

If (liste_dep.Value <> 'Sélectionner un département') Then
Sheets('listes_cascade').Range('H5').Value = liste_dep.Value
charger_activite
Else
Sheets('listes_cascade').Range('H5').Value = ''
End If

Sheets('listes_cascade').Range('I5').Value = ''
Sheets('listes_cascade').Range('J5').Value = ''

La méthode Clear des objets ComboBox permet tout d'abord de purger les deux autres listes déroulantes de leur contenu. Il n'est pas nécessaire ici de préciser la feuille en préfixe, puisque le code est directement attaché à la feuille listes_cascade sur laquelle elles se trouvent. L'instruction If qui suit permet simplement de vérifier que la valeur choisie n'est pas la proposition par défaut. Si ce test est validé, alors nous inscrivons tout d'abord ce choix dans la cellule dédiée de la zone de critères (Range('H5').Value =). Puis nous appelons la procédure charger_activite par son nom pour poursuivre le traitement. Dans le cas contraire, nous supprimons la potentielle information dans la zone de critères (Range('H5').Value = ''). Enfin, nous n'oublions pas de réinitialiser les deux autres cellules de la zone de critères correspondant au choix de l'activité et de la ville.

Pour tester le bon déroulement, nous proposons d'ajouter un test sous forme de boîte de dialogue dans la procédure appelée. Si elle apparaît, c'est que l'exécution du code se poursuit dans la nouvelle procédure.

• Dans l'explorateur de projet, double cliquer sur le module précédemment créé,
• Entre les bornes de la procédure charger_activite, ajouter l'instruction suivante :

MsgBox 'Poursuite du traitement'

• Enregistrer les modifications et basculer sur la feuille listes_cascade,
• Dans le ruban Développeur, cliquer sur le bouton Mode Création pour le désactiver,
• Puis, choisir un département dans la première liste déroulante,

Comme vous le remarquez et comme l'illustre la capture ci-dessous, la boîte de dialogue de la fonction VBA MsgBox s'affiche en effet. Elle confirme donc que la main est passée à la procédure charger_activite pour la suite du traitement. Dans le même temps, la cellule H5 de la zone de critères est affectée au choix réalisé dans la liste déroulante.

• Basculer de nouveau dans l'éditeur de code VBA Excel (ALT + F11),
• Passer la ligne de code du MsgBox en commentaire en la préfixant d'une apostrophe,

Cette procédure doit commencer par purger les deux colonnes D et F de la feuille construction. Puis elle doit parcourir les activités correspondant au département choisi dans la base de données, pour les inscrire dans la colonne D de la feuille construction. Les doublons seront nombreux comme précédemment. Mais cette fois, nous les filtrerons à la volée par le code VBA. Aucune redondance ne sera donc inscrite. Puis nous envisagerons un tri identique à celui des départements. Enfin, nous pourrons charger la liste déroulante de ces données traitées.

Comme toujours, nous devons débuter par la déclaration des variables nécessaires au traitement.

• Entre les bornes de la procédure charger_activite, ajouter les déclarations et affectations suivantes :

Dim ligne As Integer: Dim le_dep As String
Dim chaine_act As String: Dim ligne_construct As Integer
Dim colonne As Integer: Dim plage As Range

chaine_act = ''
le_dep = Sheets('listes_cascade').liste_dep.Value

Les variables ligne et ligne_construct serviront à parcourir les lignes des feuilles respectives : bd_sorties et construction. La variable colonne parle d'elle-même. La variable plage permettra comme précédemment, de mémoriser la plage dynamique des cellules purgées des doublons. La variable de type texte le_dep permettra de mémoriser le choix du département pour la correspondance de recherche dans la base de données. De même, chaine_act permettra de stocker toutes les activités déjà récupérées pour ne pas inscrire de doublon. Nous affectons ensuite les variables de type String.

• A la suite du code, ajouter la boucle permettant de purger les colonnes D et F de la feuille construction, comme suit :

For colonne = 4 To 6 Step 2
ligne_construct = 5
While Sheets('construction').Cells(ligne_construct, colonne).Value <> ''
Sheets('construction').Cells(ligne_construct, colonne).Value = ''
ligne_construct = ligne_construct + 1
Wend
Next colonne

Il s'agit du même traitement que celui réalisé dans la procédure de démarrage. Nous partons bien de la ligne 5 de la feuille construction (ligne_construct = 5). Mais cette fois nous démarrons de la colonne 4, soit la colonne D pour ne pas toucher celle des départements déjà traités.

Il s'agit désormais de parcourir tous les enregistrements de la feuille bd_sorties et de vérifier pour chacun, si le département coïncide. Si tel est le cas, nous devons prélever l'activité pour l'inscrire à la suite dans la feuille construction, dans la mesure où elle n'a pas déjà été inscrite.

• A la suite du code, ajouter les instructions suivantes :

Sheets('bd_sorties').Select
ligne = 2: ligne_construct = 5
While Cells(ligne, 1).Value <> ''
If (Cells(ligne, 4).Value = le_dep) Then
If (InStr(1, chaine_act, Cells(ligne, 3).Value, 1) = 0) Then
chaine_act = chaine_act & '-' & Cells(ligne, 3).Value
Sheets('construction').Cells(ligne_construct, 4).Value = Cells(ligne, 3).Value
ligne_construct = ligne_construct + 1
End If
End If
ligne = ligne + 1
Wend

Nous activons tout d'abord la feuille de la base de données par la méthode Select de l'objet Sheets. Nous initialisons les références de départ à l'aide des variables ligne et ligne_construct. Nous débutons l'analyse de la base de données avec une boucle While classique (While Cells(ligne, 1).Value <> ''). Tant que la cellule de la première colonne pour la ligne en cours n'est pas vide, elle poursuit son traitement. Pour cette même ligne, nous vérifions que le département inscrit en colonne D est équivalent au choix de l'utilisateur (If(Cells(ligne, 4).Value = le_dep) Then). Ensuite nous exploitons la fonction VBA InStr pour déceler la présence de l'activité dans la chaîne de caractères qui les mémorise tour à tour. Comme nous l'avait appris la formation sur le jeu du pendu en VBA Excel, lorsque cette fonction retourne la valeur 0, cela signifie que l'occurrence cherchée n'est pas trouvée (If (InStr(1,chaine_act, Cells(ligne, 3).Value, 1) = 0) Then). Dans ce cas, nous la concaténons à la suite dans la variable chaine_act pour l'exclure lors d'un futur passage. Puis nous l'inscrivons dans la feuille construction. De fait, nous déplaçons l'indice de la variable ligne_construct en l'incrémentant. Avant de boucler le While (Wend), nous n'oublions pas d'incrémenter la variable ligne (ligne = ligne + 1) pour poursuivre la lecture de la base de données dans tous les cas.

• Enregistrer les modifications et basculer sur la feuille listes_cascade,
• Choisir un département à l'aide de la première liste, par exemple la Drôme,
• Puis, afficher la feuille construction,

Comme vous le constatez, toutes les activités de ce département, purgées des doublons, sont énumérées dans la colonne D. Finalement le traitement des doublons en amont par le code est plus efficace puisqu'aucune suppression de ligne n'est réalisée. Néanmoins il est intéressant d'avoir abordé les deux méthodes. Il nous reste à trier ces activités dans l'ordre alphabétique puis à les charger dans la liste déroulante prévue à cet effet.

• Revenir dans l'éditeur de code VBA Excel (ALT + F11),
• A la suite du code de la procédure charger_activite, ajouter les instructions suivantes :

ligne_construct = ligne_construct - 1
Sheets('construction').Select
Set plage = Range(Cells(5, 4), Cells(ligne_construct, 4))
plage.Select

ActiveWorkbook.Worksheets('construction').Sort.SortFields.Clear
ActiveWorkbook.Worksheets('construction').Sort.SortFields.AddKey := Range('D5'), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets('construction').Sort
.SetRange plage
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Il s'agit d'une simple adaptation du code permettant le tri que nous avions ajusté dans la procédure de démarrage, après avoir créé la macro automatique. Notre objet plage est cette fois défini sur la colonne D à partir de la ligne 5 (Cells(5, 4)), jusqu'à la dernière ligne non vide (Cells(ligne_construct, 4)). Du coup, le tri est initialisé sur la première cellule de la colonne (SortFields.AddKey:=Range('D5')). Puis, dans le bloc With, nous définissons les autres réglages dont la plage de cellules concernée (.SetRange plage).

Si vous retournez sur la feuille listes_cascade et que vous choisissez un nouveau département, vous constatez que les activités liées purgées de leurs doublons, sont désormais classées dans l'ordre croissant. Il ne reste plus qu'à les charger dans la liste déroulante.

• Pour cela, à la suite du code, ajouter les instructions suivantes :

ligne_construct = 4
Sheets('listes_cascade').liste_act.Clear
While Sheets('construction').Cells(ligne_construct, 4).Value <> ''
Sheets('listes_cascade').liste_act.AddItem Sheets('construction').Cells(ligne_construct, 4).Value
ligne_construct = ligne_construct + 1
Wend

Sheets('listes_cascade').Select
Sheets('listes_cascade').liste_act.ListIndex = 0

Nous purgeons tout d'abord la liste déroulante des activités de son potentiel contenu grâce à sa méthode Clear. Puis, comme précédemment, nous parcourons l'ensemble des activités recensées dans la feuille construction, grâce à une boucle While dans laquelle nous n'oublions pas d'incrémenter la variable à l'issue. Cette boucle ajoute chaque activité dans la liste déroulante, grâce à la méthode AddItem de l'objet ComboBox. Enfin, nous réactivons la feuille listes_cascade et sélectionnons le premier élément de la liste des activités, soit l'information par défaut.

• Enregistrer les modifications et basculer sur la feuille listes_cascade,
• Sélectionner un département à l'aide de la première liste déroulante,

Instantanément, la liste déroulante des activités est chargée du contenu traité et purgé depuis la feuille construction. Vous remarquez, comme l'illustre la capture ci-dessous, que seules les activités du département choisi en amont sont proposées. Les deux listes déroulantes fonctionnent donc bien en cascade.



Le code complet de la procédure charger_activite est le suivant :

Sub charger_activite()
Dim ligne As Integer: Dim le_dep As String
Dim chaine_act As String: Dim ligne_construct As Integer
Dim colonne As Integer: Dim plage As Range

'MsgBox 'Poursuite du traitement'

chaine_act = ''
le_dep = Sheets('listes_cascade').liste_dep.Value

For colonne = 4 To 6 Step 2
ligne_construct = 5
While Sheets('construction').Cells(ligne_construct, colonne).Value <> ''
Sheets('construction').Cells(ligne_construct, colonne).Value = ''
ligne_construct = ligne_construct + 1
Wend
Next colonne

Sheets('bd_sorties').Select
ligne = 2: ligne_construct = 5
While Cells(ligne, 1).Value <> ''
If (Cells(ligne, 4).Value = le_dep) Then
If (InStr(1, chaine_act, Cells(ligne, 3).Value, 1) = 0) Then
chaine_act = chaine_act & '-' & Cells(ligne, 3).Value
Sheets('construction').Cells(ligne_construct, 4).Value = Cells(ligne,3).Value
ligne_construct = ligne_construct + 1
End If
End If
ligne = ligne + 1
Wend

ligne_construct = ligne_construct - 1
Sheets('construction').Select
Set plage = Range(Cells(5, 4), Cells(ligne_construct, 4))
plage.Select

ActiveWorkbook.Worksheets('construction').Sort.SortFields.Clear
ActiveWorkbook.Worksheets('construction').Sort.SortFields.AddKey := Range('D5'), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets('construction').Sort
.SetRange plage
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

ligne_construct = 4
Sheets('listes_cascade').liste_act.Clear
While Sheets('construction').Cells(ligne_construct, 4).Value <> ''
Sheets('listes_cascade').liste_act.AddItem Sheets('construction').Cells(ligne_construct, 4).Value
ligne_construct = ligne_construct + 1
Wend

Sheets('listes_cascade').Select
Sheets('listes_cascade').liste_act.ListIndex = 0
End Sub

Gérer l'événement du choix dans la liste
Comme précédemment, au choix de l'utilisateur dans la liste déroulante des activités, nous devons déclencher un code appelant une procédure externe. Cette dernière aura pour mission de charger la liste des villes en fonction des deux sélections réalisées en amont, dans les deux premières listes déroulantes. Le principe reste le même.

• Dans le ruban Développeur, cliquer sur le bouton Mode création,
• Puis, sur la feuille listes_cascade, double cliquer sur la liste déroulante des activités,

Nous basculons donc dans l'éditeur de code VBA Excel, entre les bornes de la procédure événementielle liste_act_Change. Les instructions que nous y coderons se déclencheront au changement de valeur détecté dans cette liste déroulante. Mais avant de nous y pencher, nous devons créer la procédure externe qui sera appelée.

• Dans l'explorateur de projet, double cliquer sur le module précédemment créé,
• Puis, créer la procédure charger_villes comme suit :

Sub charger_villes()

Msgbox 'Poursuite du traitement'

End Sub

• Depuis l'explorateur de projet, double cliquer sur Feuil1 (listes_cascade),
• Entre les bornes de la procédure liste_act_Change, ajouter les instructions suivantes :

liste_villes.Clear
If (liste_act.Value <> 'Sélectionner une activité') Then
Sheets('listes_cascade').Range('I5').Value = liste_act.Value
charger_villes
Else
Sheets('listes_cascade').Range('I5').Value = ''
End If

Sheets('listes_cascade').Range('J5').Value = ''

Puisqu'un changement est opéré dans la liste des activités, nous commençons par réinitialiser celle des villes, grâce à sa méthode Clear. Comme précédemment, si la valeur choisie n'est pas l'information par défaut, nous l'inscrivons dans la zone de critères (en I5), puis nous appelons la procédure externe charger_villes. Dans le cas contraire, nous réinitialisons la valeur associée dans la zone de critères. Enfin, dans tous les cas, en dehors du if donc, nous supprimons la potentielle information de critère concernant la ville.

• Enregistrer les modifications et basculer sur la feuille listes_cascade,
• Dans le ruban Développeur, cliquer sur le bouton Mode création pour le désactiver,
• Sélectionner un département, puis sélectionner une activité,

Comme vous le constatez, la boîte de dialogue de la fonction MsgBox se déclenche à deux reprises. En effet, au choix d'un département, la liste des activités est réinitialisée, impliquant un changement de valeur. Quoiqu'il en soit, les deux sélections sont parfaitement retranscrites dans la zone de critères et nous avons la confirmation que l'exécution du code se poursuit.

Nous devons donc implémenter la procédure charger_villes. Le principe est identique au traitement de la procédure charger_activite. La différence fondamentale réside dans le double critère sur le département et l'activité à vérifier ensemble, pour charger les villes correspondantes. Nous proposons donc de recopier le code précédent et de l'adapter.

• Basculer dans l'éditeur de code Visual Basic Excel,
• Afficher le code du module et placer la ligne du MsgBox en commentaire,
• Sélectionner tout le code de la procédure charger_activite,
• Le copier (CTRL + C) et le coller (CTRL + V) dans la procédure charger_villes,
• Dans la partie déclarative, ajouter la déclaration de la variable lact en tant que String,
• Changer le nom de la variable chaine_act en chaine_villes,

Dim ligne As Integer: Dim le_dep As String: Dim lact As String
Dim chaine_villes As String: Dim ligne_construct As Integer
Dim colonne As Integer: Dim plage As Range

• Réaliser le raccourci clavier CTRL + H pour afficher la boîte de dialogue de remplacement,
• Cocher la case Procédure en cours,
• Dans la zone Rechercher, saisir chaine_act,
• Dans la zone Remplacer, saisir chaine_villes,
• Cliquer enfin sur le bouton Remplacer tout,

Nous remplaçons ainsi toutes les occurrences de l'ancienne variable par son nouveau nom.

• Dans la partie des affectations, ajouter l'initialisation de la variable lact sur le choix de l'activité réalisé depuis la liste déroulante,

chaine_villes = ''
le_dep = Sheets('listes_cascade').liste_dep.Value
lact = Sheets('listes_cascade').liste_act.Value

• Dans la double boucle qui suit, supprimer les bornes de la boucles For, soit For colonne = 4 To 6 Step 2 et Next colonne,
• Puis, dans les objets Cells, remplacer la variable colonne par le chiffre 6,

ligne_construct = 5
While Sheets('construction').Cells(ligne_construct, 6).Value <> ''
Sheets('construction').Cells(ligne_construct, 6).Value = ''
ligne_construct = ligne_construct + 1
Wend

En effet, dans ce dernier cas, seule la dernière colonne (F soit 6) de la feuille construction doit être réinitialisée.

• Doubler le critère de la boucle While qui suit afin d'ajouter la condition de vérification sur l'activité, soit And Cells(ligne, 3).Value = lact,
• Dans les objets Cells contenus, changer l'indice de colonne 3 par l'indice 5, celui des villes,
• De même, changer l'indice de colonne 4 par l'indice de colonne 6,

Sheets('bd_sorties').Select
ligne = 2: ligne_construct = 5
While Cells(ligne, 1).Value <> ''
If (Cells(ligne, 4).Value = le_dep And Cells(ligne, 3).Value = lact) Then
If (InStr(1, chaine_villes, Cells(ligne, 5).Value, 1) = 0) Then
chaine_villes = chaine_villes & '-' & Cells(ligne, 5).Value
Sheets('construction').Cells(ligne_construct, 6).Value = Cells(ligne, 5).Value
ligne_construct = ligne_construct + 1
End If
End If

ligne = ligne + 1
Wend

En effet, les villes ainsi récupérées sur le double critère doivent être inscrites en colonne F (6) de la feuille construction. Nous allons devoir réajuster cet indice de la même façon pour le tri à y enclencher.

• Dans la définition de l'objet plage, remplacer l'indice de colonne 4 par l'indice de colonne 6,

ligne_construct = ligne_construct - 1
Sheets('construction').Select
Set plage = Range(Cells(5, 6), Cells(ligne_construct, 6))
plage.Select

• Puis, remplacer la clé du tri D5 par F5,

ActiveWorkbook.Worksheets('construction').Sort.SortFields.Clear
ActiveWorkbook.Worksheets('construction').Sort.SortFields.AddKey := Range('F5'), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

• Dans le code restant, après le bloc With, remplacer les occurrences de l'objet list_act par liste_villes puis modifier l'indice 4 par l'indice 6 dans les objets Cells,

ligne_construct = 4
Sheets('listes_cascade').liste_villes.Clear
While Sheets('construction').Cells(ligne_construct, 6).Value <> ''
Sheets('listes_cascade').liste_villes.AddItem Sheets('construction').Cells(ligne_construct, 6).Value
ligne_construct = ligne_construct + 1
Wend

Sheets('listes_cascade').Select
Sheets('listes_cascade').liste_villes.ListIndex = 0

Il ne reste plus qu'à coder l'événement du choix de la ville qui consiste à inscrire la valeur dans la zone de critères, s'il ne s'agit pas de l'information par défaut.

• Enregistrer les modifications et basculer sur la feuille listes_cascade,
• Dans le ruban Développeur, cliquer sur le bouton Mode création,
• Double cliquer sur la liste déroulante des villes pour créer la procédure liste_villes_Change,
• Entre les bornes de cette procédure, ajouter le code suivant :

If (liste_villes.Value <> 'Sélectionner une ville') Then
Sheets('listes_cascade').Range('J5').Value = liste_villes.Value
Else
Sheets('listes_cascade').Range('J5').Value = ''
End If

• Enregistrer les modifications et basculer sur la feuille listes_cascade,
• Cliquer sur le bouton Mode création pour le désactiver,
• Sélectionner un département, puis une activité et enfin une ville,

Comme vous le constatez, l'organisation en cascade fonctionne à merveille. Seules les activités du département désigné sont proposées. Et seules les villes correspondant à la fois au département et à l'activité choisis sont suggérées.

Au dernier clic, la zone de critères finit de se remplir, comme l'illustre l'extrait de la capture ci-dessous.



Dans une prochaine formation, nous nous soucierons de réaliser l'extraction des données correspondant aux critères ainsi recoupés. De même, nous ferons en sorte qu'il soit possible de choisir une ville sans sélection préalable de l'activité, pour y proposer toutes les idées de sorties extraites.