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 download a sample Workbook here.
Managing names under Excel
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.
The code for the combobox
We want the following to happen:
-
Upon loading the UserForm, the Combox1 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 here
Excel
- Cascading combo boxes excel vba userform
- Dependent combo box excel vba
- Dependent combobox in excel vba userform
- Creating search and update command buttons in a userform [solved] > Forum - Excel
- How to create UserForm: in Excel, VBA > Guide
- Run Macros in Excel: online, shortcuts, VBA > Guide
- How to create calculator in Excel VBA > Guide
- Scroll mouse in ListBox [solved] > Forum - Excel
- Change date format in Excel: to dd/mm/yyyy, mm/dd/yyyy
- Copy data from one excel sheet to another: automatically
- Fix arrows keys in Excel: Scroll lock, alternative method
- How to take screenshot in Excel: shortcut, sheet, cell
- How to apply a function to multiple sheets on Excel
- Convert numbers to words in Excel: without VBA, formula
- How to change author name in Excel: spreadsheet, VBA
- How many IF statements can you nest in Excel
- How to use Excel color cell if formula
- How to display multiple columns in a validation list
- How to use an IF statement to add 1 to total
- How to perform a partial cell match in Excel?
- How to run macro when data entered in a cell
- Check if a value exists in an array VBA
- Search and find using VBA in Excel
- VBA select case like: operator, string, statement
- What is the VBA code to select last sheet in workbook
- VBA Excel color codes: index number, list
- How to insert file path in excel: cell, sheet
- How to disable auto recover in Excel
- How to enable VBA in Excel: Mac, Office 365
- How combine IF function, SEARCH and ISERROR in Excel
- How to enter multiple lines in a single Excel cell
- How to insert GIF in Excel: 365, sheet, VBA
- How to count names in Excel: formula, using COUNTIF
- Open and convert an Excel file in Notepad
- Repeat rows in Excel: based on cell value, VBA
- How to automatically transfer data between sheets in Excel
- How to manipulate data in Excel: VBA
- How to copy a Macro into a blank cell
- Insert a hyperlink in Excel: with text, to another tab
- Recover Excel file: previous version
- Run macro on opening: worksheet, workbook
- How to transfer data from one Excel sheet to another?
- Most useful Excel formulas: for data analysis
- Recalculate Excel Workbook Before Saving
- How to apply if function in Excel: with dates, with text
- Select the default number of processors in Excel
- Insert picture in Excel: cell, shortcut, using formula
- How to add sheet to workbook: VBA, Excel
- Unlock password protected Excel file: Macro
- Excel functions in French
- Excel export data: from one sheet to another
- How to add a number of days to a date in Excel
- How to fill multiple Excel sheets from master sheet
- How to copy data to another workbook: using VBA
- Transfer Excel data from one sheet to another: VBA
- Excel VBA add command button programatically
- How to change a column to numeric in Excel?
- Mark sheet grade formula in Excel: template
- How to connect VB 6.0 with MS Access
- How to remove leading apostrophe in Excel
- Select empty cell in Excel: VBA
- Excel send value to another cell
- Using VBA to find last non empty row: in column, in table
- How to show or hide formula bar in Excel: VBA, shortcut
- How to change enter key function in Excel
- How to use Excel auto numbering formula
- Conditional formatting with dates: in Excel
- How to calculate VAT in Excel: formula
- Transfer a worksheet to another Excel workbook: without VBA
- Delete duplicates in Excel: column, formula
- Send email with attachment Excel: VBA, macros
- How to Insert a Transparent Image into an Excel Document
- Compare two Excel sheets: and combine data
- Copy data from one Excel workbook to another
- Auto generate serial number in Excel: VBA, formula
- How to generate email notifications for Excel updates
- How to create a drop-down List in Excel
- Split a workbook into individual files in Excel
- How to insert blank rows using macros in Excel
- How to clear formatting in Excel
- Insert an image into an Excel comment box
- Credit summation formula
- Create new sheet based on cell value: Excel, macro
- How to insert multiple rows in Excel with a macro?
- How to change Excel date format
- How to create a timer in Excel VBA
- How to use conditional formatting in Excel
- How to count occurrences of characters and numbers in Excel
- How to split text in columns in Excel
- Shortcuts to insert in Excel
- How to copy data to multiple worksheets in Excel
- Excel IF, AND, OR, and NOT functions
- Create a new workbook and copy data in Excel via a Macro
- Update dates in Excel spreadsheet: each year, formula
- How to change row color in Excel VBA based on value