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

Get FREE Advanced Excel Exercises with Solutions!

Microsoft Excel is one of the most valuable programs available today. Using the tools and capabilities that Excel provides, it is possible to conduct an infinite number of operations on a dataset. We must create a VBA ListBox for several columns in Excel regularly. Because the ListBox can include many columns, using it to show records, for example, is a beneficial activity. This post will go through two simple methods for building a ListBox that can accommodate many columns. As a result, it is recommended that you go through these 2 easy ways to create a ListBox for Multiple Columns in Excel VBA.


2 Easy Ways to Create ListBox for Multiple Columns in Excel VBA

As an example, we will investigate a sample dataset. For instance, the below dataset has Name and Designation columns. We are going to generate VBA ListBox using all 2 of the approaches. The first approach in this article demonstrates how to construct a VBA ListBox using the Property Window. In comparison, another utilizes UserForm to generate a ListBox that executes VBA code. In addition, I have been using Microsoft Excel 365 for this essay. You are free to use whatever edition is most suitable for you.

excel vba listbox multiple columns


1. Utilize Property Window in Excel VBA to Generate ListBox

Inside the Properties window, various editable fields are displayed according to the requirements of a specific object. In this context, we will develop a VBA ListBox for multiple columns using the Property Window. Please follow these instructions attentively to complete the assignment effectively.

STEPS:

  • First, navigate to the Developer tab.
  • Second, from the Controls group, choose Insert.

Utilize Property Window in Excel VBA to Generate ListBox

  • After that, click the ListBox icon from the ActiveX Controls section.

  • Due to this, an empty ListBox will be generated, as demonstrated below.

  • Next, right-click in the box.
  • Subsequently, the context menu will pop up.
  • Later, choose the Properties option.

  • As a result, the Properties window opens.
  • Importantly, find the ColumnCount property and set the value to 2.
  • Likewise, go to the ListFillRange property and write the range. In this case, PropertyWindow!$B$4:$C$10.
  • It is crucial to notice, here PropertyWindow is the sheet name.

Utilize Property Window in Excel VBA to Generate ListBox

  • After that, close the Property window.
  • Later, go to the Developer tab and click the Design icon to exit from editing.

  • Finally, the intended output will display like below.

Output of Utilizing Property Window in Excel VBA to Generate ListBox


2. Create ListBox for Multiple Columns Through VBA UserForm

In the Visual Basic Editor, you may create a dialogue box known as a VBA UserForm and then personalize its appearance. It is an object capable of containing events and code written in VBA. This article will examine how anybody may construct a ListBox with many columns using the UserForm in Excel VBA. Please follow these steps to complete the job.

STEPS:

  • First of all, select the B4:C10 range.

Create ListBox for Multiple Columns Through VBA UserForm

  • Second, go to the Formulas tab.
  • After that, from the Defined Names group, pick Define Name.

  • Subsequently, the New Name window will appear.
  • At this time, type dataRange in the Name input section to name the range.
  • It should be noted we called our sheet VBA in this context.
  • Presently, hit the OK button.

  • Now, go to the Developer tab, followed by the Visual Basic icon.

Create ListBox for Multiple Columns Through VBA UserForm

  • After that, click Insert, then pick the UserForm option.

  • Consequently, the UserForm will open up as well as a tiny window of Toolbox window for editing purposes.
  • Later, from the Toolbox window, click on the ListBox symbol.

  • At this point, use the mouse cursor to draw a ListBox section in the UserForm area.

Create ListBox for Multiple Columns Through VBA UserForm

  • After that, double-click the ListBox field and input the following code in the module box.
Private Sub UserForm_Initialize()
With ListBox1
 .ColumnCount = 2
 .List = Range("dataRange").Value
End With
End Sub
  • Next, press F5 or click the Run icon.

  • As a consequence of this, the desired outcome will appear as demonstrated below.
  • In this way, we can create a ListBox for multiple columns in Excel VBA.

Output of Creating ListBox for Multiple Columns Through VBA UserForm

Read More: How to Create Multi Select ListBox in Excel


Download Practice Workbook

You are welcome to get a free copy of the sample workbook we referred to throughout the presentation by clicking on the link immediately below.


Conclusion

From now on, you can use the steps we discussed to create ListBox for multiple columns in Excel VBA. There are many articles like this. Keep using them, and let us know if you think of any other ways to get the work done or if you have any new ideas. Remember to leave questions, comments, or suggestions in the section below.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Lutfor Rahman Shimanto
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto, BSc, Information Technology, Jahangirnagar University, Bangladesh, has worked with the ExcelDemy project for over a year. He has written 50+ articles and provided solutions of 100+ comments for ExcelDemy. Currently, he works as an Excel & VBA Developer and provides support and solutions in the ExcelDemy Forum. He has solved 100+ ExcelDemy Forum problems. His work and learning interests are in developing various Excel & VBA and Desktop applications. Outside of work, he enjoys Chess... Read Full Bio

2 Comments
  1. I can understand easily from your content.

    Thank you

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo