If you want to Insert the item at a certain position you can use the second parameter. The ListBox will automatically add it as the last item: With ListBox You provide the item you want to add as a parameter. AddItem is normally used when the Listbox already has items and you want to add a new item. List and RowSource are much more efficient. It is very rare that you would use the AddItem property to fill the ListBox. We set the RowSource property to A2:C5 and set the ColumnHeads property to true: With ListBox1 Here is an example: We want to add the data below to our ListBox and we want A1 to C1 to be the header. For example, if your range is A2 to C5 then the column header will use the range A1 to C1: The column headers are taken from the row above the range used for the RowSource.
You can set this property in the code or in the properties window of the ListBox. The ColumnHeads property must be set to True or the headers will not appear. ListBox1.RowSource = rg.Address(External:=True)Ĭolumn headers are automatically added to the ListBox when you use the RowSource property. ' Address will be Sheet1!$A$1:$A$5 which will use Sheet1 ' Address will be $A$1:$A$5 which will use the active sheet 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 If you are using the Address of a range object with RowSource then it is important to use the External parameter. If you don’t specify the sheet the VBA will use the active sheet ListBox1.RowSource = "A1:A5" We add the RowSource range as a string like this: ListBox1.RowSource = "Sheet1!A1:A5" We can change the RowSource range but we cannot change the values in the ListBox. When we use RowSource the data in the ListBox is read-only.
If data in the Range changes then the data in the ListBox will update automatically. This is different from the List Property in that the Range is linked to the ListBox. The RowSource property allows us to add a range to the ListBox. 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: If we want to change Nelson in row 3, column 2 we do it like this: ListBox1.List(2, 1) = "SMITH" Imagine we have a ListBox with data like this: You can update individual items in the ListBox using the List Property. One advantage is that you can use the click event of the Label if you want to implement something like sorting. The best way to add column headers(and it’s not a great way) is to add Labels above the ListBox columns. The ListBox only displays column headers if you use RowSource. In this case, you need to use AddItem to add the value to the ListBox: If myRange.Count = 1 Then Sheet1.Range( "A1").Value ' Single value variable Instead, it converts the range to a string/double/date etc. Important Note: If there is only one item in a range then VBA doesn’t covert it to an array. You can also use the List property to write from the ListBox to an array or range: Range( "A1:B3").Value = ListBox1.List ListBox1.List = Array( "Apple", "Orange", "Banana") Here are some examples of using the List property: ' Add the contents of an array As Range.Value is an array you can copy the contents of any range to the Listbox. The List property allows you to add to contents of an array to a ListBox. The table below provides a quick comparison of these properties: Task The List and RowSource properties are the most commonly used.
Adding a Range using the RowSource property.Adding an array/range using the List property.One at a time using the AddItem property.There are 3 ways to add items to the VBA Listbox: The VBA ListBox Properties Quick Guide FunctionĬomboBox1.RowSource = Sheet1.Range( "A2:B3").Address The ComboBox has the ability to filter the contents when you type.The Combobox items are only visible when you click on the “down” icon. Items in the ListBox are always visible.The Listbox allows multiple selections.The ListBox is very similar to the ComboBox which also allows the user to select an item from a list of items. The ListBox can have multiple columns and so it is useful for tasks like displaying records.
The ListBox is used to display a list of items to the user so that the user can then select one or more.