Microsoft 365 Combobox sans doublon et avec auto complétion et liée à une autre combobox

[CortoXls]

Bonjour
Sur le fichier en PJ sur la feuille de saisie il y a deux combobox
une pour le code postal et une pour la ville
Je voudrais que pour le code postal il n'y ait pas de doublons et une auto-complétion pour la ville et le code postal.
Actuellement je suis en office 365 et pour l'auto-complétion pour la ville comme pour le code postal ça fonctionne mais à priori pas avec XL 2021 ou XL2016. Quel paramètre faut-il changer ?

Ensuite mon problème principal est le suivant je ne parviens pas à obtenir le résultat suivant :
1/ Soit je saisis le code postal dans la combobox1 et j'obtiens dans la combobox2 la liste des villes correspondant au code postal
2/ Soit je saisis la ville et le code postal associé s'inscrit automatiquement.--> ça fonctionne dans ce sens à condition que la cellule SaisieCP soit vide.
Pour démarrer mes test j'ai donc mis un bouton RAZ qui vide les deux cellules.

Merci de votre aide pour la suppression des doublons dans le code postal et pour obtenir la liste des villes en fonction du code postal saisi.

 

[Dranreb]

Une seule association par ligne.

 

[CortoXls]

Une seule association par ligne.

Je ne vois pas...
Ça suppose plusieurs lignes pour le même élève ?

 

[Dranreb]

Oui. C'est le plus simple à gérer.

 

[CortoXls]

Bonsoir,
C'est sûr que si tu saisis le code postal au lieu de le sélectionner c'est curieux,
car la procédure change du combo doit être adaptée à cette façon de fonctionner.

Essaies comme ceci cela devrais fonctionner

Bonjour,
Testé, ça fonctionne mais j'ai apporté quelques modifications...
Il faudrait que lorsque je commence à saisir le code postal ou la ville, l'autre champ soit annulé.
Et si je commence à écrire le code postal ou la ville, si la proposition apparaît que je puisse valider via la touche "entrée" sans avoir à valider dans la liste sur laquelle je suis.
Exemple :
Dans la CBBox code postal je commence à taper " 7 "--> le champ ville est effacé (code modifié --> OK)
Je continue de taper "7898 " --> apparait " 78980 " --> je valide par entrée sans avoir à faire le choix dans la liste déroulante et dans l'autre combobox la liste des villes apparaît ainsi que la 1ère ville de cette nouvelle liste, ici ça serait " Bréval "
Idem, dans la CBBox Ville je commence à taper " B " --> le champ code postal est effacé (code modifié --> OK)
Je continue de taper " Brév " --> apparaît " Bréval " --> je valide par entrée sans avoir à faire le choix dans la liste déroulante

En passant par combobox.click ?
Par contre je ne voit pa du tout comment l'insérer dans le code existant.

Voici le code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim idx As Long
Dim lig As Long
Dim cbcp
  If Not Intersect([I6], Target) Is Nothing And Target.Count = 1 Then
    cbcp = Sheets("Divers Listes").Range("Code_Postal").Value
    With Me.ComboBox1
        .Clear
        For lig = 1 To UBound(cbcp)
            For idx = 0 To .ListCount - 1
                If .List(idx) = cbcp(lig, 1) Then Exit For
            Next idx
            If idx > .ListCount - 1 Then .AddItem cbcp(lig, 1), idx
        Next lig
        .Visible = True
        .Activate
    End With
  Else
    Me.ComboBox1.Visible = False
   End If

If Not Intersect([I7], Target) Is Nothing And Target.Count = 1 Then
    With Me.ComboBox2
        .Clear
        cbcp = Sheets("Divers Listes").Range("Villes").Value
        For lig = 1 To UBound(cbcp)
            For idx = 0 To .ListCount - 1
                If .List(idx) > cbcp(lig, 1) Then Exit For
                If .List(idx) = cbcp(lig, 1) Then Exit For
            Next idx
        .AddItem cbcp(lig, 1), idx
    Next lig
    .Visible = True
    .Activate
    End With
  Else
    Me.ComboBox2.Visible = False
   End If

End Sub
Private Sub ComboBox1_Change()
Dim lig As Long
Dim cbcp

Range("SaisieCB2") = ""

  'If Len(ComboBox1.Value) < 5 Then Exit Sub
  Range("SaisieCB1") = Me.ComboBox1
    cbcp = Sheets("Divers Listes").Range("TableauListeVilles").Value
    With Me.ComboBox2
        .Clear
        For lig = 1 To UBound(cbcp)
            If Me.ComboBox1 = cbcp(lig, 1) Then .AddItem cbcp(lig, 2)
        Next lig
        .Visible = True
       ' .Activate
    End With
End Sub

Private Sub ComboBox2_Change()

Dim Nom  As String
Dim Ligne As Integer

Range("SaisieCB1") = ""

If Me.ComboBox2.ListIndex < 0 Then Exit Sub
Nom = Me.ComboBox2.Value
Ligne = Sheets("Divers Listes").Range("B:B").Find(Nom, , , xlPart).Row
    Sheets("Formulaire de saisie").Range("SaisieCB1").Value = Sheets("Divers Listes").Cells(Ligne, 1).Value
Range("SaisieCB2") = Me.ComboBox2

End Sub

Sub RAZ()
Range("SaisieCB1") = ""
Range("SaisieCB2") = ""

End Sub

PS : Je suppose que le module1 n'est pas utilisé.

 

[CortoXls]

Voilà. Mais je n'aime vraiment pas l'utiliser dans ce contexte. Au moins un UserForm cesse toujours d'être affiché un en cas de réinitialisation du projet VBA … Mais là je crois que ce n'est même pas utile de prendre en charge les évènements du ComboBoxLiées …

Bonjour Dranreb,

Je viens de regarder et tester le fichier, ça fonctionne évidement !
Quelques modifications d'utilisation seraient bien.
Il faudrait que lorsque je commence à saisir le code postal ou la ville, l'autre champ soit annulé.
Et si je commence à écrire le code postal ou la ville, si la proposition apparaît que je puisse valider via la touche "entrée" sans avoir à valider dans la liste.
exemple
Dans la CBBox code postal je commence à taper " 7 "--> le champ ville est effacé
Je continue de taper "7898 " --> apparait " 78980 " --> je valide par entrée sans avoir à faire le choix dans la liste déroulante et dans l'autre combobox la liste des villes apparaît ainsi que la 1ère ville de cette nouvelle liste, ici ça serait " Bréval "
Idem, dans la CBBox Ville je commence à taper " B " --> le champ code postal est effacé
Je continue de taper " Brév " --> apparaît " Bréval " --> je valide par entrée sans avoir à faire le choix dans la liste déroulante

J'ai regardé le code 😱 😱
Il est énorme, je vais essayer de le comprendre mais je ne suis pas du tout capable de le modifier pour l'instant...

Voilà. Mais je n'aime vraiment pas l'utiliser dans ce contexte. Au moins un UserForm cesse toujours d'être affiché un en cas de réinitialisation du projet VBA … Mais là je crois que ce n'est même pas utile de prendre en charge les évènements du ComboBoxLiées …

 

[Dranreb]

Votre bouton RAZ efface les ComboBox pour pouvoir changer complètement le code postal et la ville.
Lorsqu'il y a plusieurs possibilités la 1ère ville n'est pas assumée en principe. Mais la liste ne comporte que les villes du code postal choisi.
Effectivement en tapant le CP 7898 la liste commence par Bréval.
Je joins un autre essai où j'affiche toujours les deux ComboBox, sinon on ne peut pas constater les réactions de l'objet ComboBoxLiées.
J'ai aussi ajouté une recherche intuitive sur le nom de ville, qui permet d'y taper un groupe de caractères pas forcément au début …
Il n'y a pas lieu de modifier la programmation de service.

 

[gbinforme]

Bonjour Dranreb,.

J'attendais le constat du demandeur, car je n'ai peut_être pas la bonne version Excel
mais sur la dernière version du classeur je n'ai aucun résultat des combo.

 

[CortoXls]

Bonjour Dranreb,.

J'attendais le constat du demandeur, car je n'ai peut_être pas la bonne version Excel
mais sur la dernière version du classeur je n'ai aucun résultat des combo.

Désolé je n'ai pas encore eu le temps de me pencher sérieusement sur la proposition (pb santé oblige...).
Je suis sur office 365 famille.
J'essaie de regarder ça avant la fin de la semaine.

 

[CortoXls]

Désolé je n'ai pas encore eu le temps de me pencher sérieusement sur la proposition (pb santé oblige...).
Je suis sur office 365 famille.
J'essaie de regarder ça avant la fin de la semaine.

Bonjour Bernard,
Je viens de passer quelques heures sur les deux fichiers...
J'ai essayé de comprendre les macros mais elles sont beaucoup trop complexes pour moi.
Est-ce que pour mon besoin le code doit être aussi complexe ?
Je ne sais même pas comment le modifier pour l'adapter à d'autres situations...
Je ne pensais pas que ma demande allait générer un code si long et compliqué.
Merci tout de même de vous être penché sur mon sujet !

 

[Dranreb]

Bonsoir.
Il y a certainement de la programmation beaucoup plus conséquente et complexe dans la bibliothèque Excel. Mais on ne peut y accéder. Et bien, il ne faut pas d'avantage chercher à entrer dans ma programmation de service. Mais peut être que pour votre besoin, surtout si vous ne voulez finalement pas du fonctionnement standard du ComboBoxLiées, parce que vous n'avez jamais à entrer en premier le nom de la ville, par exemple, peut être que le module standard de service MSujetCBx vous suffirait peut être pour constituer une liste classée et sans doublon des codes postaux avec en regard la liste des numéros de lignes des villes. La Fonction SujetCBx renvoie cela dans un variant. …