VBA & Macro Problems

Create Dependent VBA ComboBoxes with Named Ranges in WPS Office Spreadsheets

Phi Hung Vo Nov 13, 2026 869 views

Question details

Create dependent VBA ComboBox controls in WPS Office Spreadsheets so each dropdown list updates based on the previous selection by using named ranges and INDIRECT formulas.

Create Dependent VBA ComboBoxes with Named Ranges in WPS Office Spreadsheets

Product: WPS Office Spreadsheets
Device & OS: not provided
Scenario: The user is building a VBA-enabled workbook with cascading ComboBoxes where each valid combination must show a different list.
Observed behavior: The next ComboBox must display only the named range that belongs to the selected combination.

Before you start

Enable the Developer tab in WPS Office Spreadsheets and save the workbook in a macro-enabled format. Create all required named ranges before linking the ComboBoxes.

Solution 1Recommended

Create Cascading VBA ComboBoxes with Named Ranges and INDIRECT

Use this method when each combination of previous ComboBox selections must load a different list range.

This method creates a short code from the first character of each previous selection. Each code must match a named range exactly; INDIRECT then turns that text into a live list reference.

Create a Named Range for Each Valid Selection Path

Open WPS Office Spreadsheets, click the Formulas tab, select Name Manager, and create a named range for every valid code such as M, MD, MDP, MDPNE, and MDPNEF.

Insert the First ActiveX ComboBox

Go to the Developer tab, click Insert, choose the ActiveX ComboBox control, draw the first ComboBox on the worksheet, and set its ListFillRange to the first list range.

Link the Second ComboBox with INDIRECT and LEFT

Turn on Design Mode from the Developer tab, right-click the second ComboBox, choose Properties, and set ListFillRange to =INDIRECT(LEFT(Dropdown1,1)). Replace Dropdown1 with the actual name of the first ComboBox.

Add Formulas for the Remaining ComboBoxes

Right-click the third ComboBox, choose Properties, and set ListFillRange to =INDIRECT(LEFT(Dropdown1,1)&LEFT(Dropdown2,1)). Continue adding one LEFT segment for each previous ComboBox so the result matches codes like MDP, MDPNE, and MDPNEF.

Create Cascading VBA ComboBoxes with Named Ranges and INDIRECT

Keep range names exact: If the concatenated text does not match an existing named range exactly, INDIRECT returns a reference error and the ComboBox will not populate.

WPS Office Spreadsheets VBA Macro Support

Build Cascading VBA ComboBoxes in WPS Office Spreadsheets

Use WPS Office Spreadsheets to create named ranges and INDIRECT formulas that drive dependent ActiveX ComboBoxes. Each dropdown then shows only the list that matches the current combination, preserving the same macro-enabled workflow as Microsoft Excel.

1. Enable the Developer Tab: Open WPS Office Spreadsheets, go to File > Options, enable the Developer tab, and save the workbook as a macro-enabled file.
2. Create Named Ranges: Click the Formulas tab and open Name Manager. Define each valid code such as M, MD, MDP, MDPNE, and MDPNEF, and point it to the corresponding list cells.
3. Insert ActiveX ComboBoxes: On the Developer tab, click Insert, choose ComboBox under ActiveX Controls, and place a ComboBox for each selection level on the worksheet.
4. Set ListFillRange with INDIRECT: Enter Design Mode, right-click each ComboBox, choose Properties, and use =INDIRECT(LEFT(...)) formulas to link the next ComboBox to the selected named range.
5. Test the Cascading Selection: Exit Design Mode, make selections in order, and confirm each ComboBox displays only the valid list for that combination.

Built-in Name Manager and INDIRECT support for dependent dropdown listsOpens and saves Microsoft Excel .xlsm, .xlsx, and .xls files without conversionFree, lightweight Microsoft Office alternative with a familiar ribbon UIActiveX ComboBox controls and VBA editing available in WPS Office SpreadsheetsEasy migration of existing Excel VBA projects without losing list logic

Frequently Asked Questions

Can I use this method with Form Control combo boxes instead of ActiveX ComboBoxes?

This method is designed for ActiveX ComboBoxes because you set ListFillRange in the Properties window. Form Control combo boxes have different input range settings and usually require a separate VBA procedure to update the list after a selection.

Why does the second ComboBox stay blank after I enter the INDIRECT formula?

Verify that the formula returns text exactly matching a named range. Open the Formulas tab and Name Manager to confirm the range name is spelled correctly, does not include spaces, and refers to the intended list cells.

How do I create a named range in WPS Office Spreadsheets?

Select the cells that contain the list, go to the Formulas tab, click Name Manager, choose New, enter the exact name such as MD or MDP, and save the range.

Will these dependent ComboBoxes work if I open the workbook in Microsoft Excel?

Yes. WPS Office Spreadsheets saves macro-enabled workbooks that are compatible with Excel, and the named ranges plus INDIRECT formulas continue to resolve in both applications.

Excel & Data Problems