VBA & Macro Problems

How to Format Dates Correctly in an Excel VBA UserForm ListBox

Kushani Nimanthika Oct 7, 2026 869 views

Question details

The user needs a way to properly display dates instead of general serial numbers in a VBA UserForm ListBox, and wants to filter specific columns from a source table before loading them.

How to Format Dates Correctly in an Excel VBA UserForm ListBox

Product: Microsoft Excel
Device & OS: not provided
Scenario: Loading data from a worksheet table into an Excel VBA UserForm ListBox while preserving date and currency formats, and filtering rows based on criteria like dealer or item.
Observed behavior: Dates are appearing in General format (as numerical serial numbers) because the Value2 property strips date and currency data types, and the initial table data needs to be filtered before display.

Before you start

Verify that the date values in your source worksheet are correctly formatted as Dates rather than plain text, and ensure you have basic familiarity with the VBA Editor (Alt + F11).

Solution 1Recommended

Use .Value Instead of .Value2 to Preserve Date Formatting

The .Value2 property extracts raw unformatted data from cells (turning dates into serial numbers). Using .Value ensures that the date and currency formats from the worksheet are preserved when imported into the ListBox.

When transferring data from an Excel worksheet to a VBA UserForm, developers often use properties to define what information is pulled. While .Value2 is faster for raw data, it specifically ignores Date and Currency data types.

Open the VBA Editor

Press Alt + F11 to open the Visual Basic for Applications editor and double-click your UserForm in the Project Explorer.

Locate the ListBox Initialization Code

Right-click the UserForm, select 'View Code', and find the UserForm_Initialize event or the specific sub-routine where your ListBox is being populated.

Change the Range Property

Find the line mapping the worksheet range to the ListBox. Change 'Range.Value2' to 'Range.Value'. If you are referencing a ListObject table, change it to 'DataBodyRange.Value'.

Run and Verify

Press F5 to run the UserForm. Check the ListBox columns to verify that the dates are now displaying in their standard readable format.

Use .Value Instead of .Value2 to Preserve Date Formatting

Formatting Tip: If you are iterating through individual cells instead of passing an entire array, you can also use the VBA Format() function, such as Format(Cell.Value, "mm/dd/yyyy").

Solution 2

Filter the Table First and Load via Array

To populate the ListBox with only specific rows (like specific dealers or items), filter the worksheet table first, loop through the visible cells, populate an array, and assign it to the ListBox.

Advanced Spreadsheet Tool

Seamlessly Run VBA UserForms in WPS Spreadsheet

WPS Spreadsheet offers excellent support for VBA macros and UserForms, allowing you to run complex scripts, manipulate arrays, and properly format dates inside ListBoxes with an intuitive development environment.

1. Download and Install WPS Office: Visit the official WPS website, download the free version, and follow the standard installation process.
2. Open Your Macro-Enabled File: Launch WPS Spreadsheet and open your existing .xlsm or .xlsb workbook containing your UserForm.
3. Access the Developer Tools: Navigate to the 'Developer' tab on the ribbon and click on 'Visual Basic' to open the familiar VBA Editor.
4. Execute the Code: Update your code to use .Value or array mapping, then run your UserForm seamlessly without compatibility errors.

High compatibility with Microsoft Excel VBA syntax and UserForm properties.Reliably format dates, currency, and other specialized data types.Process large datasets quickly with arrays and SpecialCells.Free to download and fully compatible with existing .xlsm files.

microsoft office alternative - wps office

Frequently Asked Questions

Why do dates appear as five-digit numbers in my VBA ListBox?

Excel stores dates internally as serial numbers (e.g., 44562). When you use the .Value2 property in VBA to extract data, it pulls this raw underlying number and drops the date formatting. Switching to the .Value property forces VBA to fetch the formatted date string instead.

Which is faster for populating a ListBox: AddItem or an Array?

Populating a ListBox by assigning a 2D array directly to the .List property is significantly faster. Using .AddItem requires a loop that updates the UserForm interface row by row, which can cause severe performance drops when handling large datasets.

How can I load only filtered data into a VBA UserForm ListBox?

First, apply an AutoFilter to your source worksheet data. Then, use the SpecialCells(xlCellTypeVisible) method in your VBA code to isolate the visible rows. Loop through these visible rows to populate a VBA array, and finally assign that array to your ListBox's .List property.

How do I fix column alignment issues in a multi-column ListBox?

Ensure your ListBox .ColumnCount property matches the number of columns in your array or source range. Additionally, set the .ColumnWidths property (e.g., "50 pt; 100 pt; 0 pt") to correctly space the data and hide any unnecessary columns.

Excel & Data Problems