Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

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.


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.


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 generate, 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

Read More: How to Use ListFillRange Property of ComboBox in Excel


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 Use VBA to Populate ComboBox List from Range in Excel


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 on the ExcelDemy Website. 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

Lutfor Rahman Shimanto

Lutfor Rahman Shimanto

Hi there! I am Lutfor Rahman Shimanto. I have completed my graduation in Information Technology from Jahangirnagar University. Currently, I am working as a technical content writer at ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of work, I enjoy Chess a lot. I am a founding member of the Jahangirnagar University Chess Club and an internationally rated chess player.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo