[VBA] combobox liées sur userform

Bonjour à tous,

Après avoir essayé une paire d'heures de trouver la solution par moi-même à l'aide des différents sujets similaires sur ce forum et d'autres, je me résous à poster ici...

La situation :

Création d'un registre d'accueil comprenant 3 feuilles :

feuille 1 = registre avec un commandbutton qui ouvre un UF pour faciliter le remplissage

feuille 2 = extraction d'une ligne du registre pour création d'une fiche individuelle

feuilles 3 = base de données

Je rencontre un problème avec la mise en place de 2 combobox dans l'UF nommées respectivement 'departement' et 'direction_orientation' et que je souhaiterais utiliser en "cascade" : un département comprend plusieurs directions, je souhaiterais que lorsque le département A est sélectionné sur le premier CB le deuxième ne liste que les directions correspondantes (par exemple departement=A / liste direction_orientation SCE1, SCE2, etc

Ce que j'ai réussi (partiellement à faire) = création de la liste sur le CB departement à partir de ma feuille DONNEES-LISTES (la liste se charge mais ne supprime pas les doublons) / code emprunté chez silkyroad - lien dans le classeur - et adapté à mes données

Ce que je n'arrive pas à faire du tout = mettre en lien ces deux CB pour que la deuxième ne se charge qu'en fonction de la première. J'ai tenté plusieurs solutions dont la création de plages nommées mais j'ai du mal suivre la procédure puisque ça n'a pas marché...

La quasi-intégralité du VBA utilisé dans ce classeur a été honteusement pompé sur différents topics des forums que j'ai consultés, je demanderai un peu d'indulgence si tout ça n'est pas optimisé et/ou si des énormités se cachent dans tout ça et je suis ouvert à toute remarque d'autant plus qu'il s'agit de la première fois que je me lance dans la création d'un UF!

Ci-joint mon fichier anonymisé pour éclaircir les choses, je n'ai aucune objection à ce que la feuille DONNEES soit modifiéeni à ce que le code soit repris à plus ou moins grande échelle au contraire

En remerciant par avance les éventuels contributeurs

Re-bonjour,

Finalement en changeant de solution et grâce au topic de Marina.w et à son bout de code (https://forum.excel-pratique.com/excel/listbox-en-cascade-67964) j'ai pu trouver une solution palliative qui me convient très bien!

Au lieu de mettre en place 2 combobox j'ai tout simplement placé mes directions dans une listbox, ce qui revient apparemment au même (en tous cas ça fait ce que je cherche)

J'ai aussi passé mes départements en plusieurs colonnes avec les directions correspondantes sur les lignes inférieures et reparamétré mes plages nommées

Cela donne :

Public Sub UserForm_Initialize()

departement.Clear
departement.List = Application.Transpose(Range("départements"))
direction.Clear
direction.MultiSelect = fmMultiSelectMulti

End Sub

Public Sub departement_change()

If departement.Value = "" Then Exit Sub
direction.Clear

'Définition de la variable
Dim NomRange As String
NomRange = CaracSpec(departement.Value)

'Application liste
If NomDefini(NomRange) Then
direction.List = Application.Transpose(Range(NomRange))
Else
direction.AddItem """Aucundépartement sélectionné"""
End If
End Sub

Function NomDefini(Nom As String) As Boolean
Dim Noms As Name
NomDefini = False
For Each Noms In ThisWorkbook.Names
If Noms.Name = Nom Then NomDefini = True: Exit Function
Next Noms
End Function

Function CaracSpec(Nom As String) As String
CaracSpec = Replace(Nom, " ", "_")
CaracSpec = Replace(CaracSpec, "-", "_")
End Function

Cela a le mérite de fonctionner bien que je ne comprenne pas tout à fait comment fonctionnent les deux fonctions créées CaracSpec & Nomdefini, je sui preneur d'une traduction de ce code :)

Idem pour cette ligne?

direction.MultiSelect = fmMultiSelectMulti

Bonjour,

Pas de chance pour moi ....., je viens de finaliser ma réponse . Puis que c'est fait je "livre" :

Modifier la Private Sub userform_initialize()

Private Sub userform_initialize()
' générer la liste des departements dans le CBB "departement"
Dim j As Integer

'Récuperer les données da la colonne P de la feuille de données
With Sheets("DONNEES-LISTES")
    For j = 1 To .Range("P" & .Rows.Count).End(xlUp).Row
        Me.departement.Value = .Range("P" & j)
  '      'Filtrer les doublons
        If Me.departement.ListIndex = -1 Then Me.departement.AddItem .Range("P" & j)
    Next j
End With
Me.departement.ListIndex = -1
'pourquoi pas de End if?
'--------------------------------------------------------------
End Sub

et ajouter la Private Sub departement_Click()

Private Sub departement_Click()
'Récuperer les données da la colonne Q de la feuille de données
'en fonction de la saisie en fonction de la cbb departement
Dim j As Integer
Me.direction_orientation.Clear
With Sheets("DONNEES-LISTES")
    For j = 1 To .Range("P" & .Rows.Count).End(xlUp).Row
        If .Range("P" & j).Value = Me.departement.Value Then
            Me.direction_orientation.Value = .Range("Q" & j)
  '         'Filtrer les doublons
            If Me.direction_orientation.ListIndex = -1 Then Me.direction_orientation.AddItem .Range("q" & j)
        End If
    Next j
End With
Me.direction_orientation.ListIndex = -1

End Sub

A+

Bonjour AlgoPlus et merci pour votre réponse malgré tout!

Je vais tout de même regarder en détail tout ça, parce que j'ai l'impression qu'en termes d'ergonomie l'utilisation d'un combobox est plus indiquée dans la saisie à la volée de nombreuses entrées - si je ne me trompe pas il n'y a pas de possibilité de saisir une partie d'un élément de liste, il faut obligatoirement aller le chercher avec les flèches sur une listbox?

En ce qui concerne le code à proprement parler (je n'ai pas le classeur sous la main pour tester tout ça), si je peux me permettre j'aurais plusieurs questions probablement basiques mais qui me permettront sûrement de mieux comprendre pour refaire si besoin dans un autre projet :

- dans les deux sub, on teste une condition if avant d'incrémenter une nouvelle valeur dans le combobox mais on ne termine pas par un end if? Il me semblait que l'un n'allait pas sans l'autre?

- à quoi exactement sert la "commande" Me?

- la procédure de departement_click (et pourquoi click et non change?) se traduirait ainsi = (?)

Private Sub departement_Click()

Dim j As Integer ' j est un nombre
Me.direction_orientation.Clear 'efface les données du combobox
With Sheets("DONNEES-LISTES")
    For j = 1 To .Range("P" & .Rows.Count).End(xlUp).Row 
        If .Range("P" & j).Value = Me.departement.Value Then
            Me.direction_orientation.Value = .Range("Q" & j)
'récupère toutes les valeurs des cellules non vides de Q    
            If Me.direction_orientation.ListIndex = -1 Then Me.direction_orientation.AddItem .Range("q" & j)
' ici je ne saisis pas exactement, à quoi correspond le -1?

        End If
    Next j
End With
Me.direction_orientation.ListIndex = -1
'pourquoi donc?

End Sub

Et enfin et surtout encore merci pour le temps passé à m'aider!

- dans les deux sub, on teste une condition if avant d'incrémenter une nouvelle valeur dans le combobox mais on ne termine pas par un end if? Il me semblait que l'un n'allait pas sans l'autre?

S'il n'y a qu'une seule instruction qui suit le "Then" et que cette instruction est sur la même ligne, on peut omettre le End If. Par exemple :

If Me.departement.ListIndex = -1 Then Me.departement.AddItem .Range("P" & j)

aurait pu s'écrire de façon plus traditionnelle :

If Me.departement.ListIndex = -1 Then 
   Me.departement.AddItem .Range("P" & j)
En If

- à quoi exactement sert la "commande" Me?

Extrait aide Excel : le mot clé Me offre la possibilité de faire référence à l'instance de la classe dans laquelle le code est exécuté

Dans les codes cités, "Me" représente la UserForm ; pas obligatoire dans ce cas puisqu'on est "dans" la Userform. On aurait pu écrire :

...
If .Range("P" & j).Value = departement.Value Then
.../...

- la procédure de departement_click (et pourquoi click et non change?) se traduirait ainsi = (?)

change se déclenchera quelque soit la saisie dans la combo. le code ne "ramènera rien" puisque une saisie fantaisiste n'aura pas d’occurrence en colonne P, ça ne servirait à rien mais il n'y aurait pas d'anomalies

Click se déclenche à la sélection d'une valeur existante. Il y aura forcément un résultat au code...

Me.direction_orientation.ListIndex = -1

'pourquoi donc?

essayez de supprimer cette instruction, vous verrez la différence....

les éléments d'une combobox ont un index qui commence à 0.

Si on force l'index à -1, on se positionne sur la "fenêtre" vide.

Lorsqu' on écrit une valeur dans la combo, si cette valeur n'existe pas dans les éléments déjà saisis, ListIndex reste à -1, sinon ListIndex prend l'index de cet élément

et pour les explications :

 For j = 1 To .Range("P" & .Rows.Count).End(xlUp).Row  'pour les lignes 1 à la dernière non vide
    If .Range("P" & j).Value = Me.departement.Value Then ' si en colonne P on a la valeur sélectionnée dans 
                                                         'la combo departement
      Me.direction_orientation.Value = .Range("Q" & j) 'on écrit dans la cbb direction la valeur de la colonne Q 

      If Me.direction_orientation.ListIndex = -1 Then Me.direction_orientation.AddItem .Range("q" & j) 'si la 
                                                                                          'valeur n'a pas déjà été
                                                                                      'enregistrée on l'enregistre
                                                                                           'ça évite les doublons
   End If
 Next j

Bonjour AlgoPlus,

Merci pour ces dernières précisions

Je clôture le sujet, pour information j'ai finalement opté pour votre solution plutôt que la mienne et indique votre réponse comme étant la résolution du post

OK ,

merci et ravi d'avoir pu aidé