The VBA ListBox is a very useful control. If you are creating any kind of UserForm application you will most likely use it.
In this post, I’m going to show you everything you need to know about the VBA ListBox so you can avoid the common pitfalls and get up and running quickly and easily.
Contents
- 1
- 2 What is the VBA ListBox used for?
- 3 The VBA ListBox Properties Quick Guide
- 4 How to Add Items to the ListBox
- 5 VBA ListBox Selected Items
- 6 Reading Data from the VBA Listbox
- 7 VBA ListBox MultiSelect
- 8 VBA ListBox Columns
- 9 VBA ListBox Column Headers
- 10 Creating a ListBox Dynamically
- 11 Loop through ListBoxes
- 12 YouTube Video
- 13 What’s Next?
What is the VBA ListBox used for?
The ListBox is used to display a list of items to the user so that the user can then select one or more. The ListBox can have multiple columns and so it is useful for tasks like displaying records.
VBA ListBox versus the VBA ComboBox
The ListBox is very similar to the ComboBox which also allows the user to select an item from a list of items. The main differences are:
- The Listbox allows multiple selections. The Combobox only allows one selection.
- Items in the ListBox are always visible. The Combobox items are only visible when you click on the “down” icon.
- The ComboBox has the ability to filter the contents when you type.
The VBA ListBox Properties Quick Guide
Function | Operation | Example |
---|---|---|
AddItem | Add an item | listbox.AddItem "Spain" |
Clear | Remove all Items | listbox.Clear |
ColumnCount | Set the number of visible columns | ComboBox1.ColumnCount = 2 |
ColumnHeads | Make the column row visible | ComboBox1.ColumnHeads = True |
List | Range to Listbox ListBox to Range | Listbox.List = Range("A1:A4").Value Range("A1:A4").Value = Listbox.List |
List | Update a column value | Listbox.List(1,2) = "New value" |
ListCount | Get the number of items | cnt = listbox.ListCount |
ListIndex | Get/set selected item | Idx = listbox.ListIndex combo.ListIndex = 0 |
RemoveItem | Remove an item | listbox.Remove 1 |
RowSource | Add a range of values from a worksheet | ComboBox1.RowSource = Sheet1.Range("A2:B3").Address |
Value | Get the value of selected Item | Dim sCountry As String sCountry = listbox.Value |
How to Add Items to the ListBox
There are 3 ways to add items to the VBA Listbox:
- One at a time using the AddItem property.
- Adding an array/range using the List property.
- Adding a Range using the RowSource property.
The List and RowSource properties are the most commonly used. The table below provides a quick comparison of these properties:
Task | RowSource | List |
---|---|---|
Column Headers | Yes | No |
Update values in ListBox | No | Yes |
Add new items | No | Yes |
Data type | Range | Array(including Range.Value) |
If source data changes | Listbox is automatically updated. | ListBox is not updated. |
VBA ListBox List Property
The List property allows you to add to contents of an array to a ListBox. As Range.Value is an array you can copy the contents of any range to the Listbox.
Here are some examples of using the List property:
' Add the contents of an array ListBox1.List = Array("Apple", "Orange", "Banana") ' Add the contents of a Range ListBox1.List = Range("A1:E5").Value
You can also use the List property to write from the ListBox to an array or range:
Range("A1:B3").Value = ListBox1.List
Important Note: If there is only one item in a range then VBA doesn’t covert it to an array. Instead, it converts the range to a string/double/date etc.
Sheet1.Range("A1:A2").Value ' Array Sheet1.Range("A1").Value ' Single value variable
In this case, you need to use AddItem to add the value to the ListBox:
If myRange.Count = 1 Then ListBox1.AddItem myRange Else ListBox1.List = myRange.Value End If
The List Property and Column Headers
The ListBox only displays column headers if you use RowSource. Otherwise, they are not available. The best way to add column headers(and it’s not a great way) is to add Labels above the ListBox columns. One advantage is that you can use the click event of the Label if you want to implement something like sorting.
Updating Items using the List Property
You can update individual items in the ListBox using the List Property.
Imagine we have a ListBox with data like this:
If we want to change Nelson in row 3, column 2 we do it like this:
ListBox1.List(2, 1) = "SMITH"
The result we get is:
The List property rows and columns are zero-based so this means row 1 is 0, row 2 is 1, row 3 is 2 and so on:
VBA ListBox RowSource
The RowSource property allows us to add a range to the ListBox. This is different from the List Property in that the Range is linked to the ListBox. If data in the Range changes then the data in the ListBox will update automatically.
When we use RowSource the data in the ListBox is read-only. We can change the RowSource range but we cannot change the values in the ListBox.
How to use RowSource
We add the RowSource range as a string like this:
ListBox1.RowSource = "Sheet1!A1:A5"
If you don’t specify the sheet the VBA will use the active sheet
ListBox1.RowSource = "A1:A5"
If you are using the Address of a range object with RowSource then it is important to use the External parameter. This will ensure that RowSource will read from the sheet of the range rather than the active sheet:
' Get the range Dim rg As Range Set rg = Sheet1.Range("A1:A5") ' Address will be $A$1:$A$5 which will use the active sheet ListBox1.RowSource = rg.Address Debug.Print ListBox1.RowSource ' Address will be [Book2]Sheet1!$A$1:$A$5 which will use Sheet1 ListBox1.RowSource = rg.Address(External:=True) Debug.Print ListBox1.RowSource
RowSource Column Headers
Column headers are automatically added to the ListBox when you use the RowSource property. The ColumnHeads property must be set to True or the headers will not appear. You can set this property in the code or in the properties window of the ListBox.
ListBox1.ColumnHeads = True
The column headers are taken from the row above the range used for the RowSource. For example, if your range is A2 to C5 then the column header will use the range A1 to C1:
Here is an example: We want to add the data below to our ListBox and we want A1 to C1 to be the header.
We set the RowSource property to A2:C5 and set the ColumnHeads property to true:
With ListBox1 .RowSource = "sheet1!A2:C5" .ColumnHeads = True .ColumnWidths = "80;80;80" End With
The result will look like this:
VBA ListBox AddItem
It is very rare that you would use the AddItem property to fill the ListBox. List and RowSource are much more efficient. AddItem is normally used when the Listbox already has items and you want to add a new item.
The AddItem property is simple to use. You provide the item you want to add as a parameter. The ListBox will automatically add it as the last item:
With ListBox .AddItem "Apple" .AddItem "Orange" End With
If you want to Insert the item at a certain position you can use the second parameter. Keep in mind that this is a zero-based position, so if you want the item in position one then the value is 0, position 2 the value is 1, and so on.
With ListBox1 .AddItem "Apple" .AddItem "Orange" ' Add "Banana" to position 1(Index 0) .AddItem "Banana", 0 End With
The order will be:
Banana
Apple
Orange
If you want to add multiple columns with AddItem then you need to use the List property after you use AddItem:
With listboxFruit .List = myRange.Value .AddItem "Banana" ' Add to the second column of 'Banana' row .List(2, 1) = "$2.99" End With
One reason for using AddItem is if you are adding from data that isn’t sequential so you cannot use the List or RowSource properties:
Dim cell As Range ' Fill items with first letter is A For Each cell In Sheet1.Range("A1:A50") If Left(cell.Value, 1) = "A" Then comboBoxFruit.AddItem cell.Value End If Next
Important Note: If you fill a ListBox with RowSource then you cannot use AddItem to add a new item. If you try you will get a “Runtime Error 70 – Permission Denied”.
VBA ListBox Selected Items
If only one item is selected then you can use ListIndex to get the selected row. Remember that it is zero-based so row 1 in the ListBox is at ListIndex 0, row 2 at ListIndex 1 and so on.
MsgBox "The selected item is " & ListBox1.ListIndex
If the ListBox has multiple columns then you can use the ListIndex and List properties together to return a value in the selected row:
' Display the value from the second column of the selected row
MsgBox ListBox1.List(ListBox1.ListIndex, 2)
If multiple items are selected then you can use the GetSelectedRows function which returns a collection of selected rows:
Sub Example() ' Store the row numbers of selected items to a collection Dim selectedRows As Collection Set selectedRows = GetSelectedRows() ' Print the selected rows numbers to the Immediate Window Dim row As Long For Each row In selectedRows ' Print to the Immediate Window Ctrl + G Debug.Print row Next row End Sub ' Returns a collection of all the selected items Function GetSelectedRows() As Collection ' Create the collection Dim coll As New Collection ' Read through each item in the listbox Dim i As Long For i = 0 To listboxFruit.ListCount - 1 ' Check if item at position i is selected If listboxFruit.Selected(i) Then coll.Add i End If Next i Set GetSelectedRows = coll End Function
Reading Data from the VBA Listbox
To read data from the ListBox we can use the ListBox.Value property. This only works when the ListBox is set to only select one item i.e. MultiSelect is set to frmMultiSelectSingle(see the section VBA ListBox MultiSelect below for more about this).
Single selection only with one column
When only one item is selected we can use the Value property to get the currently selected item:
Dim fruit As String fruit = ListBox1.Value
Keep in mind that if there are multiple columns, Value will only return the value in the first column.
Single selection only with multiple columns
If the ListBox has Multiple columns you can use the Value property to get the value in the first column. You need to read through the List property to get the values in the other column(s). The List property is essentially an array so you can treat it like one.
In the example below we read through the columns of row 1(the index of row 1 is 0):
With ListBox1 For j = LBound(.List, 2) To UBound(.List, 2) ' Print the columns of the first row to the Immediate Window Debug.Print .List(0, j) Next j End With
Normally you want to print the values in the selected row. You can use the ListIndex property to get the selected item(Note that ListIndex returns the last selected items so it won’t work where there are multiple items selected):
' ExcelMacroMastery.com
Sub ReadValuesFromSelectedRow()
' Write contents of the row to the Immediate Window(Ctrl G)
With ListBox1
For j = LBound(.List, 2) To UBound(.List, 2)
' Print the columns of the selected row to the Immediate Window
Debug.Print .List(.ListIndex, j) Next j
End With
End Sub
Multiple selections
If the ListBox has multiple selections and you want to get all the data from each then you can use the GetSelectedRows() sub from the section VBA ListBox Selected Items. This will get a collection of all selected rows. You can use this to print the data from the selected rows:
Sub PrintMultiSelectedRows() ' Get all the selected rows Dim selectedRows As Collection Set selectedRows = GetSelectedRows(Me.ListBox1) Dim i As Long, j As Long, currentRow As Long ' Read through the selected rows For i = 1 To selectedRows.Count With ListBox1 ' Get the current row currentRow = selectedRows(i) ' Print row header Debug.Print vbNewLine & "Row : " & currentRow ' Read items in the current row For j = LBound(.List, 2) To UBound(ListBox1.List, 2) ' Print the columns of the first row to the Immediate Window Debug.Print .List(currentRow, j) Next j End With Next i End Sub Function GetSelectedRows(currentListbox As MSForms.ListBox) As Collection ' Create the collection Dim coll As New Collection ' Read through each item in the listbox Dim i As Long For i = 0 To currentListbox.ListCount - 1 ' Check if item at position i is selected If currentListbox.Selected(i) Then coll.Add i End If Next i Set GetSelectedRows = coll End Function
VBA ListBox MultiSelect
We can use the MultiSelect property of the ListBox to allow the user to select either a single item or multiple items:
There are 3 selections:
- 0 = frmMultiSelectSingle – [Default]Multiple selection isn’t allowed.
- 1 = frmMultiSelectMulti – Multiple items are selected or deselected by choosing them with the mouse or by pressing the Spacebar.
- 2 = frmMultiSelectExtended – Multiple items are selected by holding down Shift and choosing them with the mouse, or by holding down Shift and pressing an arrow key to extend the selection from the previously selected item to the current item. You can also select items by dragging with the mouse. Holding down Ctrl and choosing an item selects or deselects that item.
VBA ListBox Columns
You can have multiple columns in a ListBox. For example, you can load a Range or two-dimensional array to a ListBox using List or RowSource.
Often when you load data with multiple columns only one column appears. This can be very confusing when you are using the Listbox. To get the columns to appear you have to set the ColumnCount property to the number of Columns.
You should also make sure that the ColumnWidths property is correct or one of the columns may not appear.
You can do it like this:
With listboxFruit .RowSource = "Sheet1!A2:B4" .ColumnCount = 2 .ColumnWidths = "100,100" End With
In a real-world application, you could set the RowSource and ColumnCount properties like this:
With listboxFruit .RowSource = myRange.Address(External:=True) .ColumnCount = myRange.Columns.Count End With
See the AddItem section for how to add data to the other columns when you are using the AddItem property.
VBA ListBox Column Headers
Column Headers are another confusing element of the ListBox. If you use the RowSource property to add data to the ListBox then the line above the Range will be automatically used as the header.
For the Column headers to appear the ColumnHeads property must be set to true. You can do this in the properties window of the ListBox or in the code list this:
ListBox1.ColumnHeads = True
If you use the List or AddItem property to fill the ListBox then the column headers are not available. The best solution, albeit a frustrating one, is to use labels above the ListBox. I know it sounds crazy but that unfortunately is the reality. The one advantage is that you can use the Label click event which is useful if you plan to sort the data by a column.
Creating a ListBox Dynamically
Controls are normally created at design time but you can also create them dynamically at run time:
Dim myListbox As MSForms.ListBox
Set myListbox = Controls.Add("Forms.ListBox.1")
If you want to add an event to a dynamic control you can do it like this:
- First of all create a Class like this:
Public WithEvents myListBox As MSForms.ListBox Private Sub myListBox_Change() MsgBox "Selection changed" End Sub
- Name the class clsListBoxEvents. Create a variable of this class object in the UserForm like this:
Private listBoxEvents As New clsListBoxEvents
- Attach the events to the ListBox:
Sub CreateDynamicListBox() ' Create the ListBox Dim newListBox As MSForms.ListBox Set newListBox = Controls.Add("Forms.ListBox.1") ' Add some items newListBox.List = Array("Apple", "Orange", "Pear") ' Connect the ListBox to the ListBox events class Set listBoxEvents.myListBox = newListBox End Sub
Note that you can attach events to any ListBox. It doesn’t have to be created dynamically to do this.
Loop through ListBoxes
If you want to loop through all the ListBoxes on a UserForm you can do it like this:
Dim ctrl As Variant For Each ctrl In Me.Controls If TypeName(ctrl) = "ListBox" Then Debug.Print ctrl.Name End If Next ctrl
YouTube Video
Check out this video where I use the ListBox. The source code for the video is available from here
What’s Next?
Free VBA Tutorial If you are new to VBA or you want to sharpen your existing VBA skills then why not try out this Free VBA Tutorial.
Related Training: Get full access to the Excel VBA training webinars and all the tutorials.
(NOTE: Planning to build or manage a VBA Application? Learn how to build 10 Excel VBA applications from scratch.)
Professor Kelly,
Thank you for your kind generosity and continuing willingness to share your wealth of knowledge in your latest addition to what has really become the Excel Macro University. Hope to always see you in the lab.
Thanks Ed, Glad you like it.
Thanks for the connecting the dot. Information is power please continue to connect it.
I could not agree more. Paul, you content is head and shoulders above others. A lot of VBA content on the internet seems to have bits and pieces that works but often in a suboptimal way and limit a developer’s ability to grow. The methods in which you share your knowledge help harness the capabilities of VBA to its full extent and I really appreciate you taking the time to share your knowledge. Thank you
Thanks Karolis
Hi. I have a question about ListBox.
I am trying to use :
but when i use this “ListBox.AddItem” but i got error Object required
Please help me…
You have to use the name of the ListBox. If you haven’t changed it then it is probably ListBox1.
Also ListBox1 is not supported only ListBox supported but I should declare because ListBox.MouseIcon for example not working
ListBox is the type of variable. ListBox1 is the default name. You may have used a different name.
Dim a As MSForms.ListBox
Set a = [name of your listbox]
I have created ListBox in my project then i wrote these codes
Dim a as ListBox
Set a = MyListBox
But i got type mismatch error
Dim a As MSForms.ListBox
Set a = MyListBox
i think you are missing proper identification of object.
Please test with below statement.
ListBox1.AddItem
I want to use this but i don’t know which variable or lemenet to put the instead of the ” *”‘
Dim a As ListBox
Set a = *
I’m using Office 2016 Excel VBA.
I noticed two things.
.RowSource = “CardInfo!A2:AQ5″did not work until I did the following
.RowSource =”‘CardInfo’!A2:AQ5” – single quotes around the sheet name and
.ColumnCount = 43 When I left out the column count it only showed me the first column.
Any thoughts on why?
I’ve not seen any solution to my issue. As simple s possible: Sheet2 has 31,000 rows of data. I am only interested in showing data in column C in Textbox1.
Listbox1 Rowsource = =Sheet2!A2:D31103. and Textbox1 are on Userform2.
This code only pastes the selected item in Listbox1 on the form into Textbox1.
Private Sub ListBox1_Change()
UserForm2.TextBox1 = UserForm2.ListBox1.List(UserForm2.ListBox1.ListIndex, 2)
I want it to paste any group of multiselected items into Textbox1 APPENDED to the already pasted previous items in Textbox1. To me, for this to work, any single item or group of multiselected items needs to be copied and pasted in another Blank Sheet column. H. Column H then grows as data is pasted below the last row. For me, this is complicated. Can you help me with this ? Thanks, cr
End Sub
I’m having trouble implementing this as well. A couple questions…
1) is this a MSForms or ActiveX ListBox? It appears to be a form control and I’ve implemented it as described by Paul in previous comments, but I don’t have the RowSource method available (List and AddItem are available).
2) Is there a way to change the ListBox name to something more meaningful than ListBox1? It can be changed in the range definition in excel, but this isn’t reflected in VBA where I still need to set the reference as below as I see no way to access the control properties.
What am I not getting here? I’m using office 365 if that’s an issue, or the other issue might be that I am trying to implement it directly to a worksheet rather than a userform.
Thanks,
Steve
Public Sub SetupListBox()
Dim LB_Metal As MSForms.ListBox
Set LB_Metal = Sheet1.ListBox1
Dim rList As Range
Set rList = wsVBA.Range(“MAT1_MID”)
With LB_Metal
.RowSource = rList ‘ Error 438: Object doesn’t support this property or method
.ColumnHeads = True
End With
End Sub
1. Dim fruit As String
fruit = ListBox1.Value
2. Dim fruit As String
fruit = ListBox1.Text
Example 1 returned “”
Example 2 returned “apple”
Hello, I’m currently doing a VBA project, just wondering how can i delete a data in two listbox . provided that i can match a unique key identifier for both listboxes
TIA
Hello, Thanks for such clear explanations. Hoping for some help.
I have a listbox (sourced from a datasheet).
When a record is selected, the background is ugly MS blue background, with white font. I’m assuming this is related to colourtheme or default as I’ve not coded anything related to the list box (other than it’s very basic elements).
I”ve searched for code to do this, but all solutions are extremely convoluted. There must be an element that can be changed, as without any code by me, selected_list comes up blue.
Hoping you can help.
thanks
My this code is only taking column a2:a11 .
Can you please help me to get this proper result ?
ListBox1.RowSource = “sheet1!A2:D11”
ListBox1.ColumnHeads = True
Make sure to set the column number
Primeramente, lo felicito por su labor y excelente sus explicaciones
Tengo una pregunta:
• ¿Cómo imprimir la lista que se encuentra en un ListBox?
• ¿Cómo imprimir esa lista filtrada a través de un TextBoxde ListBox?
Por la atención que le presta a la presente, quedaré muy agardecido.
Very impressed with the Data entry form Paul, I have used it but have added a copy button so you can then copy a chosen row to the end of the list where you can then edit the new row, I run lots of Cisco scripts and use this to write to text files, a lot of the scripts are very much the same, so a copy and edit really helps.
Thank You
Thanks Clive. Glad you like it.
Just a Question, can you return a range on a multiselect listbox, like when using type 8 on an Application input box ?
would it be possible to do a ms 365 version of this tutorial? I am newer to VBA, not sure if listbox can be used without linking an excel sheet. Userform has not yet output any text for my list items. Any ideas?
This code is applicable to MS365. There hasn’t been a major change to VBA for Excel in 20 years. It is most likely that there is some issue with the code you added.
Wonderful Explanation
Thanks
Hello everyone and thanks for the informative content
I have defined a search button in my form that performs a search in a list box. I want to change the background color by finding the desired item without selecting the found item. Is this possible?
Thanks a lot. I have a listbox that I would like always to have a vertical scrollbar on the “sub-box” that hold the list of items. I have looked around for days, but I cannot find out how to do “force” this. It will be awsom if you can help me with this. (I can force a vertical scrollbar to the “outside” of the form – what I want to to is to add a vertical scrollbar to the “white” area that hold the list of items.)
I have a form where I can search for a value and it pulls the data from that row into boxes I have set up. There is also a list box below which shows all the data on the sheet.
How do I get it to when I search for a value, the row is highlighted in the listbox only, not the sheet?
Thank you very much for all the information you have so generously provided on this page. I find it very useful.
I am a newbie at Excel VBA and am learning much from your website. I wish to make one request. Would you please separate the ListBox and ComboBox information in “The VBA ListBox Properties Quick Guide” table. In the present table these are all jumbled up and a bit confusing. To make it easy for myself I copied your table into a Word file and then created two tables, one for ListBox and one for ComboBox.
I hope you will consider my request and make the changes. I also wish you had covered ListRow.
Thank you.
What a great tutorial! Very well written.
One thing I didn’t see was referencing a range on a sheet but the sheet name could be any of 50 truck numbers.
In your example, you wrote: ListBox1.RowSource = “Sheet1!A1:A5”
The “truck” sheet is created when the user “uploads” their inventory. It isn’t known until the user does it.
So let’s say truck D50 (which is basically a variable and not “hard coded”) is created and has its inventory listed on a new sheet with that name within an already existing workbook.
There will only ever be 4 columns, but the amount of rows may vary.
How can I reference that variable as the sheet name?
Thank you for any help and a great website!
Hi Paul,
awesome tutorial. I have a seemingly trivial challenge for which I have not found an answer yet. I have a userform with a listbox which is populated automatically with all entries in a certain table in a worksheet. Now I would like to select one particular item in the listbox via code when initializing the userform. Using .ListIndex only draws a weak frame around the Item, but does not check the corresponding checkbox. The listbox is set to multiselect.
Best regards
Till