How to Populate Excel VBA ListBox Using RowSource

Get FREE Advanced Excel Exercises with Solutions!

In this article, I will show you how to populate VBA 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.


Download Practice Workbook

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


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.

excel vba listbox rowsource

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.

Use of Named Range in RowSource of VBA ListBox

  • Now, go to Insert > UserForm.

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

Use of Named Range in RowSource of VBA ListBox

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

Use of Named Range in RowSource of VBA ListBox

  • 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 (On the top left corner).

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

Use of Named Range in RowSource of VBA ListBox

  • 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.

Use of Named Range in RowSource of VBA ListBox

  • 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.

Use of Named Range in RowSource of VBA ListBox

  • Now, we will go to 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.

Use of Named Range in RowSource of VBA ListBox

  • Now, save it by clicking on the Save icon.

  • Now, run the UserForm1 by clicking the Run icon.

Use of Named Range in RowSource of VBA ListBox

  • 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.

Use of Named Range in RowSource of VBA ListBox

Read More: Create ComboBox with RowSource in Excel VBA (2 Easy Ways)


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

Use of Direct Range Reference in RowSource of VBA ListBox

  • 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.

Use of Direct Range Reference in RowSource of VBA ListBox

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

Read More: Create ListBox for Multiple Columns in Excel VBA (2 Easy Ways)


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.

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. Finally, please visit Exeldemy for more exciting articles on Excel.


Related Articles

Aniruddah Alam
Aniruddah Alam

Hi, I am Md. Aniruddah Alam from Dhaka, Bangladesh. I completed my Bachelor's degree in Naval Architecture from BUET. Currently, I am working as an Excel and VBA Content Developer. Here, I always try to present solutions to Excel-related problems in a very concise and easy-to-understand manner. In my leisure time, I love to read books, listen to podcasts, and explore new things.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo