Fix an Excel UserForm ListBox That Shows Only the Last Value in WPS Spreadsheets
Question details
The user needs to populate an Excel UserForm ListBox with values from column B that begin with the text entered in a TextBox, while clearing previous results so old matching values do not remain visible.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A user is building or troubleshooting a VBA UserForm in WPS Spreadsheets and wants to filter column B values dynamically as text is entered in a TextBox.
- Observed behavior
- The ListBox shows only the last matching value or leaves old filtered results visible because the code does not clear the ListBox before adding new items.
Open the VBA editor in WPS Spreadsheets and confirm that the UserForm contains the ListBox named Li1 and the TextBox named Tx16. Also verify that the values to filter are located in column B of the intended worksheet.
Clear the ListBox and fully qualify worksheet references before adding filtered values
Use this solution when the UserForm ListBox keeps only the final match or old results stay visible while you type in the TextBox.
The most common cause is that the ListBox is not cleared before each new set of AddItem calls. Full worksheet qualification also ensures the macro reads column B from the correct sheet instead of relying on the active sheet.
In WPS Spreadsheets, press Alt+F11 to open the VBA editor, double-click the UserForm in the Project Explorer, select the Tx16 TextBox, and open its Change event.
Inside the event, add an If block: if Me.Tx16.Value is empty, hide Li1 and exit the sub; otherwise set Me.Li1.Visible to True and set Me.Li1.Height to 77.
Call Me.Li1.Clear after the visibility check and before the loop, so every previous filtered result is removed from the ListBox.
Use a worksheet object such as worksheet2, find the last used row with worksheet2.Range("B1000").End(xlUp).Row, and loop from row 2 to that last row.
For each row, compare Left(.Cells(c, "B").Value, Len(Me.Tx16.Text)) with Me.Tx16.Text, and use Me.Li1.AddItem .Cells(c, "B").Value for each matching value.
Populate a UserForm ListBox Correctly with WPS Spreadsheets VBA
Open the UserForm VBA project in WPS Spreadsheets, add the clear and loop logic to the TextBox Change event, and run the macro to populate the ListBox with every matching column B value instead of only the last one.
- 1. Install WPS Office: Go to the official WPS Office download page, download the installer, and install WPS Office on your computer.
- 2. Open the macro-enabled workbook: Launch WPS Spreadsheets, open the .xlsm or .xls workbook that contains the UserForm, and enable macros if prompted.
- 3. Open the VBA editor and select the UserForm: Press Alt+F11 to open the VBA editor, then select the UserForm and open the Tx16 TextBox Change event.
- 4. Paste and adjust the ListBox filtering code: Add the ListBox clear and column B loop code to the Change event, then save the workbook in a macro-enabled format.
- 5. Run and test the UserForm: Show the UserForm or run the macro, type in Tx16, and verify that Li1 displays every matching column B value, not just the last one.
Frequently Asked Questions
Why does my UserForm ListBox show only the last value?
Usually the ListBox is not cleared before new AddItem calls, so the control keeps the last matching value or old entries remain visible. Add Me.Li1.Clear before the loop that adds items.
How do I hide the ListBox when the TextBox is empty?
In the Tx16 Change event, use an If block: when Me.Tx16.Value is empty, set Me.Li1.Visible to False and exit the sub; otherwise set it to True before refreshing results.
What does it mean to qualify a worksheet reference in VBA?
It means using a specific worksheet object such as worksheet2.Range("B1000") or With worksheet2 ... End With instead of relying on ActiveSheet, so the code always reads column B from the intended sheet.
Can I use this UserForm ListBox filtering code in WPS Spreadsheets?
Yes. Open the macro-enabled workbook in WPS Spreadsheets, press Alt+F11 to open the VBA editor, add or modify the code in the UserForm, and save the file in a macro-enabled format.
How do I avoid looping through empty rows in column B?
Find the last used row first with worksheet2.Range("B1000").End(xlUp).Row or use worksheet2.Cells(worksheet2.Rows.Count, "B").End(xlUp).Row, then loop from row 2 to that last row only.
