How to Populate Excel VBA ListBox Using RowSource

In this article, I will show you how to populateVBA ListBox in an Excel Userform using RowSource. In many situations, we need to represent existing data in VBA Userform Listbox. Instead of manually typing the data to be displayed, we can use the RowSource option. To know more, read this article carefully.

2 Useful Methods to Populate Excel VBA ListBox Using RowSource

In this section, I will discuss 2 methods to use RowSource in VBA ListBox. For illustration purposes, I have taken a dataset of some students with their Student ID, First Name, Last Name, and Gender.

Now, we want to show some portion of this table in a VBA Userform ListBox ( for example, B4:C9). To do that either we can first rename the portion to be displayed (our 1st method) and use it in RowSource or we can directly use the range reference in RowSource (2nd method). Let’s explore our 1st method.

1. Use of Named Range in RowSource of VBA ListBox

In this method, we will rename the range that we want to display in the User form ListBox using RowSource. To do that, read the following steps.

Steps:

First, let’s create a Userform. To do that open the Visual Basic Editor by clicking Alt+F11.

Now, go to Insert > UserForm.

As a result, a new UserForm will be created. Now, from the toolbox select the ListBox icon and then draw a box inside UserForm1 Window.

As a result, we will see a ListBox window named ListBox1 as in the picture below.

Now, in the ListBox, we need to input the data that we want to show ( In this case B4:C9). To do that, first, go to the sheet and select the range you want to display in the form (In this case B4:C9). Then, go to the Name Box (In the top left corner).

Now, give a suitable name to it. I renamed the selected range as Display_Text.

Then go to the VBA editor again. After that, left-click on the ListBox1. You should see a property tab on the left side of the VBA window.

If you scroll down below in the Properties tab, you should see the RowSource option.

Now, write down the name of the range to be displayed in the opposite cell of RowSource. As a result, we should see the chosen range inside the ListBox window.

Now, we will go to the column count to input the number of columns that our selected range contains. In my case, I have 2 columns, so I am inserting 2.

Now, save it by clicking on the Save icon.

Now, run the UserForm1 by clicking the Run icon.

As a result, you will see that, in the main worksheet, the form will appear containing the data that you have assigned using RowSource.

Here, we can see that the name of the form (UserForm1) is displayed in the top left corner. This is called Caption. If we want to change the caption, we have to change it from the Properties tab of Userform1.

2. Use of Direct Range Reference

Another way to use the RowSource in ListBox for populating Forms is by inputting the range address directly in the RowSource box. This method is completely similar to the previous method except in the step when we input the address in the RowSource box. Hence, I am describing those steps only.

Steps:

Follow the same steps described in method 1 until we input the data address in the RowSource box.
In the RowSource box, write down the following Reference.

Sheet1!B4:C9

Now, follow the rest of the steps in method 1 to run the UserForm1. As a result, you will get the same result as method 1.

In this way, we can obtain a similar result of displaying data directly from the worksheet into UserForm by using ListBox RowSource.

Things to Remember

If you do not find the Properties tab on the left pan, right-click on the ListBox1 window and choose Properties. As a result, the Properties tab will come up.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

RowSource in ListBox.xlsm

Conclusion

That is the end of this article regarding how to populate Excel VBA ListBox using RowSource. If you find this article helpful, please share this with your friends. Moreover, do let us know if you have any further queries.

Get FREE Advanced Excel Exercises with Solutions!