VBA & Macro Problems

How to Populate an Excel VBA ListBox Based on a Selected Date

Natalie Taylor Oct 21, 2026 868 views

Question details

The user needs to populate a VBA ListBox in an Excel UserForm with records that exactly match a date entered in a TextBox.

How to Populate an Excel VBA ListBox Based on a Selected Date

Product: Excel
Device & OS: not provided
Scenario: Filtering data records in a UserForm ListBox based on a dynamically entered date in a text field.
Observed behavior: The current VBA code fails to filter the records by the date and consistently displays only the first row of the database.

Before you start

Ensure your Excel workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have a backup of your data before editing the VBA code.

Solution 1Recommended

Use the TextBox AfterUpdate Event to Filter Records

Triggering the macro on the AfterUpdate event ensures the date entry is fully complete before the code attempts to filter records, preventing partial input errors.

The 'Change' event fires with every single keystroke. This means a partially typed date will cause the filter macro to run prematurely, leading to incorrect or failed matches. Switching to the 'AfterUpdate' event forces the code to wait until the user finishes typing or leaves the field before executing the search.

Open the VBA Editor

Press 'Alt + F11' on your keyboard to open the Visual Basic for Applications editor, then double-click your UserForm in the Project Explorer.

Change the Event Procedure

Locate the macro code for your date TextBox. Change the event signature from 'Private Sub TextBox1_Change()' to 'Private Sub TextBox1_AfterUpdate()'.

Validate the Date Input

Inside the AfterUpdate subroutine, add a validation check using 'If IsDate(TextBox1.Value) Then' to ensure the user has entered a properly formatted date.

Convert Text to Date Format

Use the 'CDate(TextBox1.Value)' function to convert the text string into an actual Date data type. Loop through your worksheet's date column to compare against this converted date.

Populate the ListBox Array

Include logic to clear previous searches using 'ListBox1.Clear'. If a match is found in the loop, assign the matching records to the ListBox array using 'ListBox1.AddItem'.

Use the TextBox AfterUpdate Event to Filter Records

Data Type Formatting: Always ensure the date format in your worksheet strictly matches the format expected by the CDate function based on your local regional system settings.

Powerful Spreadsheet Data Management

Manage Data and Forms Easily with WPS Spreadsheet

WPS Spreadsheet provides robust data management features and excellent compatibility with Microsoft Excel files, allowing you to run your UserForms, ListBoxes, and data-filtering macros effectively.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your .xlsm file containing the UserForm and ListBox data.
2. Enable Developer Tools: Click the 'Developer' tab on the ribbon and ensure Macros are enabled for the current session to allow background scripts to run.
3. Access the Macro Editor: Click 'Visual Basic' or press Alt+F11 to open the WPS Macro Editor interface.
4. Run your Form: Make the necessary AfterUpdate changes to your TextBox code, save your modifications, and run your UserForm directly within WPS Spreadsheet.

Fully compatible with Microsoft Excel file formats (.xls, .xlsx, .xlsm)Comprehensive data filtering, sorting, and validation tools built-inFamiliar user interface with zero learning curve for seamless migration

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VBA ListBox remain empty even when the dates visually match?

This is usually caused by a data type mismatch. The TextBox outputs a text string, while the worksheet holds a serial date number. You must use the CDate() function in VBA to convert the TextBox string to a recognizable date type before comparing.

What is the primary difference between the TextBox Change and AfterUpdate events in VBA?

The 'Change' event triggers immediately after every single character is typed or deleted in the TextBox. The 'AfterUpdate' event only triggers when the user presses Enter or moves focus away from the TextBox, making it the ideal choice for evaluating complete inputs like dates.

How do I clear previous search results from a ListBox before running a new date query?

You should use the ListBox1.Clear method at the beginning of your search macro. Place this command right before the loop that populates the ListBox with the new matching date records to prevent appending new results to the old ones.

Excel & Data Problems