VBA & Macro Problems

How to Load Multiple Excel Rows into a VBA UserForm ListBox

Natalie Taylor Oct 22, 2026 868 views

Question details

The user needs to display multiple rows and columns from an Excel worksheet simultaneously inside a VBA UserForm ListBox.

How to Load Multiple Excel Rows into a VBA UserForm ListBox

Product: Excel
Device & OS: not provided
Scenario: Developing an Excel VBA UserForm to display a dataset with multiple columns instead of viewing one record at a time.
Observed behavior: Needs to populate a ListBox with a multi-column range (e.g., A1:D20) properly formatted, ensuring the column count matches and avoiding permission errors.

Before you start

Ensure the Developer tab is enabled, your workbook is saved as a Macro-Enabled Workbook (.xlsm), and you have inserted a UserForm containing a ListBox control named ListBox1.

Solution 1Recommended

Use the UserForm Initialize Event to Load Data into the ListBox

The most efficient way to load a multi-column range into a ListBox is by setting the ColumnCount and assigning the range values to the .List property during form initialization.

By utilizing the UserForm_Initialize event, you can automatically load your desired worksheet range into the ListBox as soon as the form opens. It is crucial to set the correct column count to match your data structure.

Open the VBA Editor

Press Alt + F11 to open the Visual Basic for Applications (VBA) editor. Double-click your UserForm in the Project Explorer, right-click the form, and select 'View Code'.

Create the Initialize Event

From the top-right dropdowns in the code window, select 'UserForm' and then 'Initialize' to create the Private Sub UserForm_Initialize() event.

Set the ColumnCount Property

Inside the event, set the column count to match the data you are importing. For example, add the code: ListBox1.ColumnCount = 4.

Load the Excel Range

Assign the worksheet range directly to the List property using code like: ListBox1.List = Sheets("Source").Range("A1:D20").Value. Ensure the sheet name 'Source' matches your actual worksheet.

Select the Final Item (Optional)

If you need the ListBox to scroll automatically to the last entry, add the following line: ListBox1.TopIndex = ListBox1.ListCount - 1.

Use the UserForm Initialize Event to Load Data into the ListBox

Avoiding Run-time error 70: If you encounter 'Run-time error 70: Permission denied', check the ListBox properties window and clear any text in the 'RowSource' field. Mixing RowSource with VBA .List or .AddItem methods causes this error.

Advanced Data Management

Create VBA UserForms Easily in WPS Spreadsheet

WPS Spreadsheet provides robust support for VBA macros, allowing you to create custom UserForms, ListBoxes, and automate data handling seamlessly.

1. Open the Developer Tools: Launch WPS Spreadsheet, go to the 'Developer' tab on the ribbon, and click 'Visual Basic' to open the VBA Editor.
2. Insert a Custom UserForm: Click 'Insert' > 'UserForm' from the top menu, then drag and drop a ListBox from the Toolbox onto your new form.
3. Write the Initialization Code: Right-click the UserForm, select 'View Code', and paste your standard Excel VBA code to populate the ListBox exactly as you would in Microsoft Office.

Fully compatible with Microsoft Excel VBA syntax and UserForm controls.Lightweight and fast macro execution for heavy datasets.Intuitive Developer tools for form controls and ListBox configuration.Free to use for everyday office productivity and automation tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get 'Run-time error 70: Permission denied' when loading data into a ListBox?

This error typically occurs when the RowSource property of the ListBox is statically set in the Properties window, but you are also trying to populate the ListBox dynamically using .AddItem or .List in the VBA code. Clear the RowSource property in the properties panel to fix this.

How do I ensure the ListBox shows all columns from my Excel data?

You must set the ColumnCount property. Select the ListBox in the VBA Editor, open the Properties window (F4), and change ColumnCount to the exact number of columns in your range (e.g., set it to 4 for range A1:D20). You can also do this in VBA using ListBox1.ColumnCount = 4.

Can I load specific rows conditionally instead of the whole range?

Yes. Instead of assigning the entire range to the .List property at once, you can use a For...Next loop to iterate through the Excel rows and use ListBox1.AddItem along with ListBox1.List(row, col) to add only the rows that meet your specific criteria.

Excel & Data Problems