In this article you will learn how to fill a cascading combo box using the indirect method.
You can download a sample Workbook here.
To define names in an Excel Workbook, you can either:
For Excel 2007 or earlier version: Insert > Names > Define.
Most current versions (from Excel 2010): Ribbon formulas > Define a name.
Upon loading the UserForm, the Combox1 will be filled.
The other combo boxes will be filled based on the values of the first one.
To fill a combo box with the contents of a named range of the workbook, the syntax is:
Private Sub UserForm_Initialize() ComboBox1.Clear ComboBox1.List = Application.Transpose(Range("Dep")) ComboBox2.Clear ComboBox3.Clear End Sub
When a value is selected in the first drop-down list, it will (the text shown in the Combobox) correspond to a workbook name.
To display the contents of the cells of the named range, we will use the Change event:
Private Sub ComboBox1_Change() 'Combobox département Avoid the bug generated when a user deleted the content of ComboBox1 If ComboBox1.Value = "" Then Exit Sub ComboBox2.Clear ComboBox3.Clear ComboBox2.List = Application.Transpose(Range(NomRange)) End Sub
Private Sub ComboBox2_Change() 'Combobox communes If ComboBox2.Value = "" Then Exit Sub ComboBox3.Clear ComboBox3.List = Application.Transpose(Range(NomRange)) End Sub
The name entered in the Combobox won't match any workbook name. This happens when the name hasn't been defined. To circumvent this problem, we will create a small function to loop through all the names of the workbook:
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
As you will notice in the sample file, defining names do not take into account special characters or spaces. In some situations, you may need to edit the variables. Here's an example:
Function CaracSpec(Nom As String) As String CaracSpec = Replace(Nom, " ", "_") CaracSpec = Replace(CaracSpec, "-", "_") End Function
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
Download the sample sheet here
Photo: © Everypixel
DON'T MISS