VBA - How to create a cascading combo box in a userform
VBA - How to create a cascading combo box in a userform
Introduction
In this article you will learn how to fill a cascading combo box using the Indirect method.Prerequisites
- 1 workbook
- 1 UserForm
- 3 combobox.
You can also downloaded a sample workbook here: http://cjoint.com/14au/DHhootCLYVh.htm
Managing names under Excel
To define names in an Excel workbook, you can either:- Excel 2007 or earlier version: Insert > Names > Define.
- As from Excel 2010: Ribbon formulas > Define a name.
The code for the combobox
We want the following to happen:- Upon loading the UserForm, the ComboBox1 will be filled.
- The other combo boxes will be filled based on the values of the first one.
Filling the first list
To fill a combo box with the contents of a named range of the workbook, the syntax is:- ComboBox1.List = Application.Transpose(Range("monNom"))
To clear the ComboBox:
- Combobox1.Clear
What gives us the code for filling the first dropdown list at the loading of the UserForm:
Private Sub UserForm_Initialize()
ComboBox1.Clear
ComboBox1.List = Application.Transpose(Range("Dep"))
ComboBox2.Clear
ComboBox3.Clear
End Sub
Filling the 2nd list
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
For the third combo box:
Private Sub ComboBox2_Change() 'Combobox communes If ComboBox2.Value = "" Then Exit Sub ComboBox3.Clear ComboBox3.List = Application.Transpose(Range(NomRange)) End Sub
Common bugs
Unnamed Range
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
Input error
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
The completed code
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 link
Download the sample sheet on this link: http://cjoint.com/?DHhootCLYVhVBA - How to create a cascading combo box in a userform
Introduction
In this article you will learn how to fill a cascading combo box using the Indirect method.Prerequisites
- 1 workbook
- 1 UserForm
- 3 combobox.
You can also downloaded a sample workbook here: http://cjoint.com/14au/DHhootCLYVh.htm
Managing names under Excel
To define names in an Excel workbook, you can either:- Excel 2007 or earlier version: Insert > Names > Define.
- As from Excel 2010: Ribbon formulas > Define a name.
The code for the combobox
We want the following to happen:- Upon loading the UserForm, the ComboBox1 will be filled.
- The other combo boxes will be filled based on the values of the first one.
Filling the first list
To fill a combo box with the contents of a named range of the workbook, the syntax is:- ComboBox1.List = Application.Transpose(Range("monNom"))
To clear the ComboBox:
- Combobox1.Clear
What gives us the code for filling the first dropdown list at the loading of the UserForm:
Private Sub UserForm_Initialize()
ComboBox1.Clear
ComboBox1.List = Application.Transpose(Range("Dep"))
ComboBox2.Clear
ComboBox3.Clear
End Sub
Filling the 2nd list
When a value is selected in the first drop-down list, it will (the text shown in the ComboBox) corresponds 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
For the third combo box:
Private Sub ComboBox2_Change() 'Combobox communes If ComboBox2.Value = "" Then Exit Sub ComboBox3.Clear ComboBox3.List = Application.Transpose(Range(NomRange)) End Sub
Common bugs
Unnamed Range
The name entered in the ComboBox does not match any workbook name because it has not been define yet. To circumvent this problem, we will create a small function. Itsrole is 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
Input error
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 variable:Here's an example:
Function CaracSpec(Nom As String) As String CaracSpec = Replace(Nom, " ", "_") CaracSpec = Replace(CaracSpec, "-", "_") End Function
The completed code
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 link
Download the sample sheet on this link: http://cjoint.com/?DHhootCLYVhRelated
- Cascading combo boxes excel vba userform
- Dependent combobox vba - Best answers
- Excel vba userform dependent combobox - Best answers
- Excel vba userform search box ✓ - Forum - Excel
- Excel vba userform examples download - How-To - Excel
- Excel vba userform minimize button - How-To - Excel
- Excel vba userform stopwatch - How-To - Excel
- Excel vba userform - How-To - Excel
This document, titled « VBA - How to create a cascading combo box in a userform », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).
