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.
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.
- First, navigate to the Developer tab.
- Second, from the Controls group, choose Insert.
- 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.
- 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.
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.
- First of all, select the B4:C10 range.
- 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.
- 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.
- 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.
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.
- How to Add ComboBox in Excel (3 Easy Methods)
- Excel VBA to Populate ComboBox from Dynamic Range
- How to Create a Searchable ComboBox with VBA in Excel
- Excel VBA ComboBox: Important Properties to Explore
- How to Clear Items from VBA ComboBox in Excel
- Insert ComboBox with Listindex in Excel VBA (with Easy Steps)
- How to Use ComboBox to Get Selected Item Utilizing VBA