VBA Excel - Combobox en cascade dans un userform

Décembre 2016



Introduction

Cette fiche pratique fait suite à cette discussion, et après l'étude de cette astuce.
Il s'agit d'une méthode parmi d'autres, de remplissage des combobox, avec choix dépendant d'une autre, basé sur la technique Indirect d'Excel.

Pré-requis

  • 1 classeur,
  • 1 UserForm,
  • 3 combobox.

Si vous le souhaitez, le classeur disponible en téléchargement est "clé en main"...

Gérer les noms sous Excel

Pour définir des noms dans un classeur excel, deux possibilités d'accès :
  • Versions d'Excel < 2007 => Insertion/Noms/Définir
  • Versions > 2007 : Ruban formules / définir un nom.

Pour cela, merci de vous reporter au tutoriel cité plus haut par ICI.

Le code des combobox

Nous souhaitons, au chargement de l'UserForm, que seule soit remplie la combobox1. Les autres, dépendantes du choix fait dans la première, se doivent d'être vides.

Remplissage de la 1ère liste

Pour remplir une combobox avec le contenu d'un range "nommé" dans le classeur, la syntaxe est :
ComboBox1.List = Application.Transpose(Range("monNom"))
Pour vider une ComboBox :
Combobox1.Clear
Ce qui nous donne le code de remplissage de la première liste déroulante, au chargement de l'UserForm :
Private Sub UserForm_Initialize()
ComboBox1.Clear
ComboBox1.List = Application.Transpose(Range("Dep"))
ComboBox2.Clear
ComboBox3.Clear
End Sub

Remplissage de la 2nde liste

Lorsque l'on choisit une valeur dans la première liste déroulante, ce choix (le texte indiqué dans la ComboBox) corresponds à un nom du classeur. Il nous faut donc juste afficher le contenu des cellules de ce Range nommé.
Pour ceci nous allons utiliser l'événement Change de la première liste déroulante, comme ceci :
Private Sub ComboBox1_Change() 'Combobox département
'ici on évite un bug si l'utilisateur efface le contenu de ComboBox1
If ComboBox1.Value = "" Then Exit Sub
ComboBox2.Clear
ComboBox3.Clear
ComboBox2.List = Application.Transpose(Range(NomRange))
End Sub


Idem pour la troisième qui est fonction de la seconde :
Private Sub ComboBox2_Change() 'Combobox communes
If ComboBox2.Value = "" Then Exit Sub
ComboBox3.Clear
ComboBox3.List = Application.Transpose(Range(NomRange))
End Sub

Bugs possibles

Range non nommé

Le nom saisi dans la ComboBox ne correspond à aucun nom du classeur parce qu'il n'a pas encore été défini.
Pour contourner ce problème, nous allons créer une petite fonction. Son rôle va être de boucler sur tous les noms du classeur et de vérifier si le nom saisi est bien le nom d'un Range nommé de ce classeur.
Le code de cette fonction :
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

Erreur de saisie

Vous le verrez dans l'exemple en téléchargement, la définition des noms ne prends pas en compte les caractères spéciaux, ni les espaces.
Il est donc, par moment, nécessaire de procéder à des petites modifications sur les variables, pour "faire coller" le tout.
En voici un exemple :
Function CaracSpec(Nom As String) As String
CaracSpec = Replace(Nom, " ", "_")
CaracSpec = Replace(CaracSpec, "-", "_")
End Function

Code en intégralité

Ce code est le code de l'exemple mis en téléchargement.
Option Explicit

Private Sub UserForm_Initialize()
ComboBox1.Clear
ComboBox1.List = Application.Transpose(Range("Dep"))
ComboBox2.Clear
ComboBox3.Clear
End Sub

Private Sub ComboBox1_Change() 'Combobox département
If ComboBox1.Value = "" Then Exit Sub
ComboBox2.Clear
ComboBox3.Clear
Dim NomRange As String
NomRange = CaracSpec(ComboBox1.Value)
If NomDefini(NomRange) Then
    ComboBox2.List = Application.Transpose(Range(NomRange))
Else
    ComboBox2.AddItem """Aucune commune"""
End If
End Sub

Private Sub ComboBox2_Change() 'Combobox communes
If ComboBox2.Value = "" Then Exit Sub
ComboBox3.Clear
Dim NomRange As String
NomRange = CaracSpec(ComboBox2.Value)
If NomDefini(NomRange) Then
    ComboBox3.List = Application.Transpose(Range(NomRange))
Else
    ComboBox3.AddItem """Aucune rue"""
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

Téléchargement

Ce classeur reprend l'exemple de Raymond PENTIER en y ajoutant les noms des rues de deux villes, à titre d'exemple.
Lien de téléchargement du classeur

A voir également :

Ce document intitulé «  VBA Excel - Combobox en cascade dans un userform  » issu de CommentCaMarche (www.commentcamarche.net) est mis à disposition sous les termes de la licence Creative Commons. Vous pouvez copier, modifier des copies de cette page, dans les conditions fixées par la licence, tant que cette note apparaît clairement.