ListBox [lsb]
ListBox vs ComboBox
A listbox only lets you choose from a pre-defined list. You cannot type/enter a different value.
A combobox allows the user to either select an item from a drop-down list or to enter a different value into the textbox.
Adding to single column
You can use the "AddItem" method when you have a single column listbox.
If you try to add items to a listbox that has a non empty RowSource property you will get a "permission denied" error.
lsbListBox1.AddItem "Item 2"
Currently Selected Item
Obtaining the currently selected item in a single selection list box
Call MsgBox [lsbListBox1.Value]Call MsgBox [lsbListBox1.List[lsbListBox1.ListIndex]]
Multiple Columns
A listbox can contain multiple columns by using the ColumnCount property.
You can use the "AddItem" combined with the List property when you have multiple columns.
All list entries start with a row number of 0, and a column number of 0, ie List[0,0] = "text"
If you want to add items to a multi column listbox, you need to use "AddItem" to add a new row and then either "List" or "Column" to add the specific items past the first column.
lsbListBox1.ColumnCount = 3
lsbListBox1.ColumnWidths = "50,50,50"
For icount = 1 to 25
lsbListBox1.AddItem
lsbListBox1.List[iCount - 1, 0] = "Item " & iCount
lsbListBox1.List[iCount - 1, 1] = "Item " & iCount
lsbListBox1.List[iCount - 1, 2] = "Item " & iCount
Next iCount
Both column and row numbers in a listbox start at 0 by default and not 1.
The only way to obtain the selected items in a multiple selection list box is to cycle through the whole list.
For iCount = 0 To lsbListBox1.ListCount - 1
If [lsbListBox1.Selected[iCount] = True] Then
Call MsgBox[lsbListBox1.List[iCount]]
End If
Next icount
Adding using an Array
If you data is stored in a one-dimensional array you can assign the array directly using the List property.
Dim iCount As IntegerDim vDataArray1[100]
For iCount = 0 to 100
vDataArray1[iCount] = iCount
Next iCount
lsbListBox1.List = vDataArray1
If you data is stored in a two-dimensional array you can assign the array directly using the List property.
lsbListBox1.ColumnCount = 2lsbListBox1.ColumnWidths = "50,50"
Dim iCount As Integer
Dim vDataArray2[1 To 50, 1 To 2]
For iCount = 1 to 50
vDataArray2[iCount, 1] = iCount
vDataArray2[iCount, 2] = iCount & "b"
Next iCount
lsbListBox1.List = vDataArray2
Removing Selected
This will remove the currently selected item
lsbListBox1.Remove[lsbListBox.ListIndex]More than 10 Columns
If you want to have more than 10 columns in your listbox then you must use the List Property.
Dim myArray[] As String
Dim iRow As Integer
Dim iCol As Integer
ReDim myArray[1 To 5, 1 To 13]
iRow = 1
While [iRow < 6]
lsbListBox1.AddItem
For iCol = 1 To 13
myArray[iRow, iCol] = "Col" & iCol
Next iCol
iRow = iRow + 1
Wend
lsbListBox1.ColumnHeads = True
lsbListBox1.ColumnCount = 13
lsbListBox1.ColumnWidths = "50,50,50,50,50,50,50,50,50,50,50,50,50"
lsbListBox1.List = myArray
End Sub
TextColumn
This property allows you to display one set of values to the user but return a different value when selection has been made.
Use the Text property to return the column specified in the TextBound column.
If you use the Value property you will always get the item in the first column.
BoundColumn
The BoundColumn property identifies which column is referenced when you refer to the Value property of a listbox entry.
No items selected
It is possible to display a listbox with no items selected [when the listindex = -1].
Although once an item is selected it is not possible to unselect all the items.
Multiple selections
By default only a single item can be selected although this can be changed by changing the MultiSelect property.
You can only make multiple selections with a listbox - not a combo box.
RowSource
The items in a Listbox can be retrieved from an Excel range of cells by using the RowSource property.
Make sure you include the worksheet name otherwise the active sheet will be used.
If you populate a listbox using the RowSource method you then can't populate a second listbox using the "List" method.
If you populate a listbox using the RowSource method you cannot use the RemoveItem method.
Adding Column Headers
You can only display column headers when you use the RowSource property, not when you use an array or add items individually.
To display column headers set the ColumnHeads property to True.
Do not include the column headings on the worksheet in the range defined for RowSource.
The row directly above the first row of the RowSource will be automatically used.
Adding Unique Items
You should add all the items to a collection ensuring that only unique items get added and then add all the items from the collection to the listbox.
Dim objCell As RangeDim colNoDuplicates As New Collection
Dim vItem As Variant
On Error Resume Next
For Each objCell In Range["A1:A12"]
colNoDuplicates.Add objCell.Value, CStr[objCell.Value]
Next objCell
For Each vItem In colNoDuplicates
UserForm1.lsbListBox1.AddItem vItem
Next vItem
UserForm1.Show
It might also be worth sorting the collection before you add it to the listbox.
lsbListBox1.ToplsbListBox1.TakeFocusOnClick = False
Change the Integral Height to False and a line is roughly 13.42
Arial, 10, Regular
It is possible to have a drop-down listbox - change the property - doesn't have to be a combo box !!
It is possible to display equally spaced items in a list box by using a monospaced font such as Courier New. A better approach is to use multiple columns.
Do you have to populate a listbox with data to be able to assign an array to it ???? I DON'T THINK YOU DO !!
The vertical height of a listbox in design mode may not be the same height when the userform is actually displayed.