Liste VBA Excel des villes selon le code postal client

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


Sujets que vous pourriez aussi aimer :


Villes et codes postaux en VBA

Dans cette formation VBA Excel, nous poursuivons nos travaux de conception pour notre application de gestion et de facturation des clients. Dans le volet précédent, nous avons chargé les listes déroulantes des identifiants. Elles permettent désormais de définir un client à facturer et de spécifier les articles à ajouter à la commande. Dans cette suite, nous proposons d'améliorer l'ergonomie du système permettant de créer un nouveau client. Des villes sont associées à des codes postaux. A la saisie de l'un d'entre eux, nous souhaitons proposer automatiquement les communes correspondantes.

Source et problématique
Nous devons commencer par réceptionner les travaux précédents.

• Télécharger le classeur codes-postaux-et-villes-vba.xlsm en cliquant sur ce lien,
• Double cliquer sur le fichier téléchargé pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Une erreur se déclenche précisément à cause de ce verrou.

• Valider le message d'alerte puis fermer Excel,
• Double cliquer de nouveau sur le fichier téléchargé pour le rouvrir dans Excel,

Cette fois le chargement s'opère sans conflit. La feuille Facture est active par défaut.

• Déployer la première liste déroulante en cellule B5,
• Puis, choisir un identifiant client dans la liste des propositions,

Comme vous le remarquez, les informations attachées sont instantanément importées dans les zones de saisie prévues à cet effet.

• Déployer la seconde liste déroulante en cellule G5,
• Puis, choisir une référence article dans la liste des propositions,

Le constat est le même. Les données de l'article sont automatiquement rapatriées. Ici, dans le cas d'un nouveau client à créer, à la saisie du code postal en cellule D5, nous souhaitons charger une liste déroulante des villes correspondantes en cellule E5. L'objectif est d'homogénéiser et de sécuriser l'inscription des informations.

Mais, comme un code VBA précédent écrit directement dans cette cellule, au choix d'un identifiant client, nous ne pouvons pas choisir un ComboBox ActiveX. Nous proposons de construire une liste déroulante classique se nourrissant des informations d'une plage de cellules à actualiser. Cette dernière permettra de faire un choix ou de réceptionner une saisie.

• En bas de la fenêtre Excel, cliquer sur l'onglet Communes pour activer sa feuille,

Nous y découvrons la base de données de toutes les références, pour la région PACA. Il s'agit du secteur d'activité de l'entreprise. Le tableau est trié dans l'ordre croissant sur les codes postaux en colonne D. Ce tri simplifiera l'algorithme du traitement VBA destiné à trouver toutes les correspondances d'un code postal.

Extraire les villes d'un code postal
Ce traitement doit intervenir sur un évènement précis. Il concerne la saisie validée du code postal en cellule D5 de la feuille Facture. Au travers de la construction de jeux, comme celui du casse brique pour Excel, nous avions appris à gérer les interactions de l'utilisateur avec les cellules de la feuille. Nous proposons de charger la colonne H de la feuille Communes, des villes restituées, en partant de la cellule H1. Cette plage dynamique servira de source de données à la liste déroulante à bâtir.

• 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 Feuil1 (Facture),

Nous affichons ainsi la feuille de code associée au contexte qui nous intéresse.

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

De cette manière, nous créons instantanément la procédure évènementielle Worksheet_SelectionChange:

Private SubWorksheet_SelectionChange(ByVal Target As Range)

End Sub

Mais l'évènement à gérer n'est pas celui que nous souhaitons. Nous voulons intercepter la modification d'une saisie dans une cellule précise.

• En haut de la feuille de code, déployer la seconde liste déroulante, sur la droite,
• Dans les propositions, cliquer sur l'évènement Change,

Nous créons ainsi la procédure évènementielle Worksheet_Change :

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Vous notez la présence du paramètre Target déclaré et transmis. Il cible la cellule dont le contenu a été modifié. Il s'agit d'un objet de type Range. Grâce à lui, nous pouvons connaître les coordonnées (Row et Column) de la cellule concernée. Celle qui nous intéresse est la cellule D5. Nous ne devons donc agir que lorsque l'indice de ligne est 5 et que dans le même temps, l'indice de colonne vaut 4.

• Dans les bornes de la procédure évènementielle, saisir le code VBA suivant :

...
Dim ligne_bd As Integer: Dim ligne_liste As Integer
Dim indicateur As Byte

ligne_bd = 3: ligne_liste = 1: indicateur = 0

If (Target.Row = 5 And Target.Column = 4) Then


End If
...

Nous déclarons deux variables pour parcourir les lignes. La première est destinée à passer en revue les enregistrements de la feuille Communes, à la recherche des villes concordantes. La seconde doit permettre de construire la plage de cellules des communes restituées pour remplir la liste déroulante. Cette dernière n'existe d'ailleurs pas encore. La variable indicateur est déclarée comme un entier court. Elle porte bien son nom et est initialisée à zéro. Tant qu'elle n'a pas changé, nous savons que le code postal n'a pas été trouvé. Une fois la concordance établie, nous basculerons sa valeur à 1. Et lorsque nous aurons dépassé les codes postaux correspondants, nous la basculerons à deux. Cette valeur indiquera que le traitement de la boucle doit s'interrompre.

Grâce à une instruction conditionnelle au double test, nous nous assurons que la cellule est bien celle du code postal. Ainsi et le cas échéant, nous n'enclenchons pas de traitement inutile.

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

...
Sheets('Communes').Cells(ligne_liste, 8).EntireColumn.Delete

While indicateur < 2

If (Sheets('Communes').Cells(ligne_bd, 4).Value = Range('D5').Value) Then
indicateur = 1
Sheets('Communes').Cells(ligne_liste, 8).Value = Sheets('Communes').Cells(ligne_bd, 3).Value
ligne_liste = ligne_liste + 1
End If
If (indicateur = 1 And Sheets('Communes').Cells(ligne_bd, 4).Value <> Range('D5').Value) Then
indicateur = 2
End If
ligne_bd = ligne_bd + 1
if(ligne_bd>1000) then indicateur = 2
Wend
...

Tout d'abord, plutôt que de purger la colonne des anciennes communes potentiellement importées pour un autre code postal, nous choisissons de la supprimer. Située sur la droite de la base de données, elle n'impacte ni la structure ni l'intégrité de l'information. Nous exploitons la propriété EntireColumn sur une cellule de la rangée. Elle désigne la colonne intégrale. De fait, la méthode Delete engage la suppression de cette dernière.

Nous engageons ensuite un traitement VBA récursif qui doit se poursuivre tant que la variable indicateur ne vaut pas 2. Si le code postal saisi est équivalent au code postal en cours d'analyse dans la base de données, nous enclenchons trois actions. Tout d'abord nous basculons la valeur de la variable indicateur à 1. Nous savons ainsi que nous sommes dans la zone des codes correspondants. Ensuite, nous inscrivons la ville associée à la suite dans la colonne H de la feuille Communes, grâce à la variable ligne_liste. C'est pourquoi, nous l'incrémentons enfin à chaque nouvelle insertion. Toute nouvelle commune sera de fait inscrite à la suite.

Dans un deuxième test, nous vérifions que les codes postaux ne correspondent pas, quand dans le même temps, la variable indicateur vaut 1. Cette double condition permet de comprendre que la zone des codes postaux concordants a été dépassée et que le traitement récursif peut être stoppé.

Dans tous les cas, à chaque passage dans la boucle, nous n'omettons pas d'incrémenter la variable ligne_bd. Enfin, nous terminons par une sécurité visant à interrompre la boucle en cas de mauvaise saisie et donc de non correspondance.

• Enregistrer les modifications et basculer sur la feuille Facture,
• En D5, taper par exemple le code postal 04110, sans oublier de le valider,
• Puis, en bas de la fenêtre Excel, cliquer sur l'onglet Communes pour activer sa feuille,

En colonne H, vous notez effectivement l'extraction de toutes les villes correspondant au code postal saisi depuis la feuille Factures. Ce sont ces communes que nous devons suggérer à l'utilisateur, par le biais d'une liste déroulante, lors de la création d'un nouveau client.

Liste évolutive des villes
Le nombre de villes attachées à un code postal est variable. La hauteur de la liste déroulante doit donc s'adapter dynamiquement. Et pour cela, nous proposons de nommer la plage d'extraction et de la retravailler avec la fonction Decaler. Nous l'avions notamment démontrée à l'occasion de la formation Excel sur la construction de listes dynamiques.

• Sélectionner la première ville extraite soit la cellule H1,
• Dans la zone Nom, en haut à gauche de la feuille, taper le texte Villes et valider par Entrée,

Cette validation est nécessaire pour que le nom attribué soit pris en compte. Cependant à ce stade, il ne désigne que la première cellule de la plage d'extraction. C'est là que doit intervenir la fonction Decaler pour adapter la hauteur de cette plage à son contenu :

=Decaler(cellule_de_départ; decalage_ligne; decalage_colonne; [Hauteur]; [Largeur])

En premier paramètre, nous devons lui indiquer le point de départ, soit la cellule déjà mentionnée. Les deux arguments suivants doivent être réglés à zéro. Aucun décalage ni en ligne ni en colonne n'est à opérer par rapport à cette cellule. Les deux derniers paramètres sont facultatifs. Mais l'avant dernier est prépondérant dans notre cas. Il permet de spécifier la hauteur variable de cette liste. Nous la déterminerons grâce à la fonction Excel NbVal. Cette dernière renvoie le nombre de cellules non vides, soit la hauteur de la plage.

• 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,
• Dans la boîte de dialogue qui suit, sélectionner le nom Villes,
• Dans la zone Fait référence à, adapter la syntaxe comme suit :

=Decaler(Communes!$H$1;0;0;NbVal(Communes!$H:$H))



Grâce à ce dernier argument, nous adaptons la hauteur de la plage d'extraction en fonction du nombre de villes inscrites. En construisant la liste déroulante sur ce nom, cette dernière devrait s'adapter en hauteur de la même façon.

• Cliquer sur le bouton à la coche verte pour valider la syntaxe de la plage nommée,
• Puis, cliquer sur le bouton Fermer de la boîte de dialogue pour revenir sur la feuille Excel,
• En bas de la fenêtre Excel, cliquer sur l'onglet Facture pour revenir sur sa feuille,
• Sélectionner ensuite la case de la ville à renseigner, soit la cellule E5,
• En haut de 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,
• Activer l'onglet Options de la boîte de dialogue qui s'affiche,
• Dans la zone Autoriser, choisir Liste,
• Puis, cliquer dans la zone Source juste en-dessus pour l'activer,
• Saisir alors la syntaxe suivante : =Villes,

Par cette équivalence, nous stipulons que la liste déroulante doit se remplir des villes de la plage dynamique, reconnue par ce nom.

• Activer l'onglet Alerte d'erreur de la boîte de dialogue,
• Dans la zone Styles, choisir Informations,

Nous avions expliqué ce réglage dans une autre formation. Il permet à l'utilisateur d'inscrire dans la cellule, d'autres valeurs que celles prévues par la liste déroulante.

• Cliquer sur le bouton Ok pour valider et revenir sur la feuille Excel,

Si vous déployez la liste déroulante, vous constatez qu'elle est parfaitement ajustée aux villes extraites.

• En cellule D5, taper un nouveau code postal comme 04000 et le valider,
• Puis, cliquer sur la flèche de la liste déroulante pour la déployer,

La liste reste muette. Notre mécanisme ne semble plus fonctionner. Pourtant, des villes sont bien associées à ce code postal.

• 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,
• Dans la boîte de dialogue, sélectionner la plage Villes,

Comme vous le constatez, notre formule que nous avions pourtant bien écrite, comporte désormais des erreurs. Ce phénomène s'explique logiquement. Cette plage est définie sur la colonne H. Dans l'intervalle, notre code VBA supprime cette dernière. Même si elle est remplacée, la liaison est rompue.

Il est intéressant d'avoir commis cette erreur pour la comprendre et l'anticiper à l'avenir. Mais avant d'ajuster le code VBA, nous devons corriger la formule de la plage.

• Dans la zone Fait référence à, corriger la syntaxe comme suit :

=Decaler(Communes!$H$1;0;0;NbVal(Communes!$H:$H))

• Valider par la coche verte et cliquer sur le bouton Fermer,
• Revenir dans l'éditeur VBA entre les bornes de la procédure Worksheet_Change,
• Remplacer la ligne supprimant la colonne H par les instructions VBA suivantes :

...
While Sheets('Communes').Cells(ligne_liste, 8).Value <> ''
Sheets('Communes').Cells(ligne_liste, 8).Value = ''
ligne_liste = ligne_liste + 1
Wend
ligne_liste = 1
...

Plus classiquement donc, nous parcourons toutes les cellules non vides de la colonne H afin de réinitialiser leur contenu. A l'issue, nous n'oublions pas de réaffecter la variable ligne_liste sur sa valeur d'origine.

• Enregistrer les modifications et basculer sur la feuille Facture,
• En cellule D5, taper un code postal différent comme : 83440, puis le valider,
• Déployer ensuite la liste déroulante des villes,

Cette fois, toutes les villes concordantes sont parfaitement proposées. La formule de la plage nommée n'a pas été détériorée. De fait, la source est parfaitement définie et s'adapte en hauteur, en fonction du nombre de communes restituées.

Si vous tapez une ville non suggérée, un message apparaît. Mais à validation, la saisie est acceptée. Si vous choisissez un identifiant avec la première liste déroulante, en même temps que les informations sont rapatriées, la liste des villes est reconstruite en fonction du code postal importé.

Le code VBA complet de la procédure évènementielle permettant de construire la liste des communes attachées est le suivant :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ligne_bd As Integer: Dim ligne_liste As Integer
Dim indicateur As Byte

ligne_bd = 3: ligne_liste = 1: indicateur = 0

If (Target.Row = 5 And Target.Column = 4) Then

While Sheets('Communes').Cells(ligne_liste, 8).Value <> ''
Sheets('Communes').Cells(ligne_liste, 8).Value = ''
ligne_liste = ligne_liste + 1
Wend
ligne_liste = 1

While indicateur < 2

If (Sheets('Communes').Cells(ligne_bd, 4).Value = Range('D5').Value) Then
indicateur = 1
Sheets('Communes').Cells(ligne_liste, 8).Value = Sheets('Communes').Cells(ligne_bd, 3).Value
ligne_liste = ligne_liste + 1
End If
If (indicateur = 1 And Sheets('Communes').Cells(ligne_bd, 4).Value <> Range('D5').Value) Then
indicateur = 2
End If
ligne_bd = ligne_bd + 1
If (ligne_bd > 1000) Then indicateur = 2
Wend

End If
End Sub

Rendez-vous dans la prochaine étape pour poursuivre la construction de notre application Excel de gestion et de facturation.