VBA & Macro Problems

How to Format a VBA UserForm Date from a ListBox in Excel

Camila Milosovich Oct 23, 2026 868 views

Question details

The user needs to populate an Excel VBA UserForm text box with a date selected from a ListBox, formatted as dd mmm yy instead of an Excel serial number.

How to Format a VBA UserForm Date from a ListBox in Excel

Product: Excel
Device & OS: not provided
Scenario: Editing a record via a VBA UserForm where date values are retrieved from a ListBox selection and populated into a date field.
Observed behavior: The date retrieved from the ListBox displays in the UserForm as a raw 5-digit Excel serial number (e.g., 44247) rather than a human-readable date.

Before you start

Ensure your Excel Developer tab is enabled and that you are familiar with accessing your UserForm code within the Visual Basic for Applications (VBA) editor.

Solution 1Recommended

Use the VBA Format Function to Set ListBox Date Values

Apply Excel's built-in VBA Format function when assigning the ListBox value to your TextBox to ensure proper date representation.

Excel stores dates internally as sequential serial numbers. When extracting a date directly from a ListBox array into a TextBox, it defaults to this 5-digit number representation.

To resolve this, you must explicitly format the value during the assignment step. Additionally, remember that ListBox column indexes are zero-based, meaning the first column is always accessed using index 0.

Open the VBA Editor

Press Alt + F11 to open the Visual Basic for Applications (VBA) editor, and navigate to the code module for your UserForm.

Locate the population event

Find the event subroutine where the UserForm fields are populated from the ListBox, such as a ListBox_Click or ListBox_DblClick event.

Apply the Format function

Use the Format function to assign the formatted date to your text box. Use this exact syntax: Me.txtdate.Value = Format(Me.lstactivity.List(Me.lstactivity.ListIndex, 0), "dd mmm yy").

Avoid double assignment operators

Ensure your code does not contain invalid syntax with two equal signs, such as .Value = CDate(...) = ..., as this will evaluate to a boolean instead of assigning the date.

Use the VBA Format Function to Set ListBox Date Values

Zero-Based Indexing: ListBox arrays always start at index 0. If your target date is stored in the first column of the ListBox, you must always use 0 as the column reference.

Advanced VBA Support

Create and Run VBA Macros Smoothly with WPS Spreadsheet

WPS Spreadsheet fully supports Excel VBA macros, UserForms, and advanced formatting. You can build, edit, and troubleshoot your ListBox data seamlessly in a lightweight environment.

1. Install WPS Office: Download and install the free WPS Office suite on your computer.
2. Open your Macro-Enabled Workbook: Launch WPS Spreadsheet and open your existing .xlsm or .xlsb file.
3. Access the Developer Tools: Navigate to the Developer tab on the ribbon and click on the VBA Editor button to view your macros.
4. Edit UserForm Code: Modify your UserForm ListBox code directly within the WPS VBA environment using the exact same syntax as Excel.

Full compatibility with Microsoft Excel VBA syntax and logicSupports complex UserForms, ListBoxes, and custom formatting controlsLightweight installation with fast execution for complex macrosFree to download with a familiar spreadsheet interface

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Excel date show up as a 5-digit number like 44247?

Excel natively stores dates as sequential serial numbers starting from January 1, 1900, so they can be easily used in calculations. To display them as a readable date in a UserForm, you must apply the VBA Format function when fetching the value.

How do I select the second column of a ListBox in VBA?

ListBox column indexes are zero-based. To reference the second column, you must use index 1 instead of 2. For example, use Me.ListBox1.List(Me.ListBox1.ListIndex, 1).

Can I format the UserForm date as mm/dd/yyyy instead?

Yes, you can easily change the format string inside the VBA code. Simply use Format(Me.lstactivity.List(Me.lstactivity.ListIndex, 0), "mm/dd/yyyy") to display the date in a standard US format.

Excel & Data Problems