[Excel VBA] How to Display a Sheet Table in a UserForm ListBox (Multi-Column)

You often want to display Excel sheet data, such as product lists or customer directories, directly in a VBA UserForm.

The “ListBox” control supports multiple columns. By setting just a few properties, you can replicate the worksheet table exactly on your form.

In this article, I will explain the basic and powerful method to display a multi-column table from a sheet in a ListBox.

VBA Sample Code to Display Sheet Table in ListBox

It is best to perform this setting within the UserForm_Initialize event, which runs just before the form appears.

In this example, we will display a product list located in the range A1:C10 of the sheet named “ProductList” into the ProductListBox on the ProductListForm.

UserForm Code

' Event executed when the form is initialized
Private Sub UserForm_Initialize()
    
    Dim dataRange As Range
    
    ' Set the range of the sheet table you want to display
    Set dataRange = ThisWorkbook.Worksheets("ProductList").Range("A1:C10")
    
    With Me.ProductListBox
    
        ' .ColumnCount property: Set the number of columns to display
        .ColumnCount = 3
        
        ' .ColumnWidths property: Set the width of each column in points
        ' Specify by separating with semicolons (;)
        .ColumnWidths = "120;80;50"
        
        ' .List property: Assign cell range values to the list all at once
        .List = dataRange.Value
        
    End With
    
End Sub

Preparation Before Execution

1. Create a worksheet named “ProductList” and enter some table data (e.g., Product ID, Name, Price) in the range A1 to C10.
2. Create a UserForm named “ProductListForm”.
3. Place a “ListBox” on the form and change its (Name) property to ProductListBox.
4. Paste the code above into the form’s code module. When you display the form, the sheet’s table will appear in the ListBox.

Explanation of the Code (Properties)

.ColumnCount = 3

Specifies the number of columns to display in the ListBox. Set this number to match the number of columns in your original table (3 columns: A, B, and C in this example).

.ColumnWidths = "120;80;50"

Specifies the width of each column as a string using points.

• Separate the width of each column with a semicolon (;), like "120;80;50".
• The number of values in this string must match the value of .ColumnCount.
• Adjust the numbers to find the best fit for your data.

.List = dataRange.Value

This single line is the core part that pours the sheet data into the ListBox.

• dataRange.Value: The .Value property of a cell range returns a 2-dimensional array containing the values of all cells in that range.
• The ListBox’s .List property can accept this 2-dimensional array directly and set the contents in a table format all at once.

This method is much faster and more efficient than looping through cells one by one and adding them using the .AddItem method.

Summary

In this article, I explained how to display Excel sheet data directly in a multi-column ListBox.

1. Specify the number of columns with .ColumnCount.
2. Specify the width of each column with .ColumnWidths (semicolon-separated).
3. Assign the .Value of the desired cell range directly to the .List property.

With these three steps, you can easily handle sheet data on interactive forms. This is a basic technique that can be used in many VBA tools for selecting and viewing data.