Vba listbox selection change event
ListBox (lsb)
ListBox vs ComboBoxA listbox only lets you choose from a pre-defined list. You cannot type/enter a different value. Adding to single columnYou can use the "AddItem" method when you have a single column listbox. lsbListBox1.AddItem "Item 2" Currently Selected ItemObtaining the currently selected item in a single selection list box Call MsgBox (lsbListBox1.Value)Call MsgBox (lsbListBox1.List(lsbListBox1.ListIndex)) Multiple ColumnsA listbox can contain multiple columns by using the ColumnCount property. 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. 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 ArrayIf 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 SelectedThis will remove the currently selected item lsbListBox1.Remove(lsbListBox.ListIndex)More than 10 ColumnsIf 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 TextColumnThis property allows you to display one set of values to the user but return a different value when selection has been made. BoundColumnThe BoundColumn property identifies which column is referenced when you refer to the Value property of a listbox entry. No items selectedIt is possible to display a listbox with no items selected (when the listindex = -1). Multiple selectionsBy default only a single item can be selected although this can be changed by changing the MultiSelect property. RowSourceThe items in a Listbox can be retrieved from an Excel range of cells by using the RowSource property. If you populate a listbox using the RowSource method you then can't populate a second listbox using the "List" method. Adding Column HeadersYou can only display column headers when you use the RowSource property, not when you use an array or add items individually. Adding Unique ItemsYou 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 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 Properties
Methods
Events
© 2021 Better Solutions Limited. All Rights Reserved. © 2021 Better Solutions Limited TopPrevNext |