ListBox (lsb)


ListBox - This control allows the user to select from a list of possible choices.


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.


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 1" 
lsbListBox1.AddItem "Item 2"


Currently Selected Item

Obtaining the currently selected item in a single selection list box

lsbListBox1.Value 
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.

Dim icount As Integer 
For icount = 0 To lsbListBox1.ListCount - 1
    If lsbListBox1.Selected(icount) = True Then
   End If
Next icount


Adding using an Array

If you data is stored in an array (one or two dimensional) you can assign the array directly using the List property.

Dim vDataArray(100) 
lsbListBox1.List = vDataArray

Dim vDataArray(1 To 50, 1 To 2)
lsbListBox1.List = vDataArray


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.

lsbListBox1.RowSource = "Sheet1!A1:A12" 

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 Range 
Dim 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.Top 
lsbListBox1.TakeFocusOnClick = False


Properties

BackColorSpecifies the background color of the object.
BorderColorSpecifies the color of an object's border.
BorderStyleSpecifies the type of border used.
BoundColumnIdentifies the source of data in a multicolumn ListBox.
Cancel 
ColumnProvides a reference to a specific column when you have a multiple column listbox.
ColumnCountSpecifies the number of columns to display in a list box.
ColumnHeadsDisplays a single row of column headings for list boxes.
ColumnWidthsSpecifies the width of each column in a multicolumn list box. Inlcuding the "pt" is optional.
ControlSourceIdentifies the data location used to set or store the Value property of a control. The ControlSource property accepts worksheet ranges from Microsoft Excel.
ControlTipTextSpecifies text that appears when the user briefly holds the mouse pointer over a control without clicking.
Default 
EnabledSpecifies whether a control can receive the focus and respond to user-generated events.
FontDefines the characteristics of the text used by a control.
ForeColorSpecifies the foreground color of an object
Height 
HelpContextID 
IMEModeSpecifies the default run time mode of the Input Method Editor (IME) for a control. This property applies only to applications written for the Far East and is ignored in other applications.
IntegralHeightIndicates whether a ListBox or TextBox displays full lines of text in a list or partial lines.
LayoutEffect 
Left 
List 
ListCount(read-only) Returns the number of items in the listbox.
ListIndexSpecifies which item is selected in the listbox. This is an integer from 0 to to the total number of items minus 1.
ListStyleSpecifies the visual appearance of the list in a ListBox , either frmListStylePlain or fmListStyleOption. If you have a listbox which has its liststyle set to option, then to remove the horizontal scrollbar, change to columnwidths property to the width - 16.
LockedSpecifies whether a control can be edited.
MatchEntryReturns or sets a value indicating how a ListBox searches its list as the user types, either fmMatchEntryFirstLetter, fmMatchEntryComplete or fmMatchEntryNone
MouseIconAssigns a custom icon to an object.
MousePointerSpecifies the type of pointer displayed when the user positions the mouse over a particular object. Can be any of the fmMousePointer constants.
MultiSelectIndicates whether the object permits multiple selections.
Name 
Object 
OldHeight 
OldLeft 
OldTop 
OldWidth 
Parent 
RowSourceLinks the control to a range of cells on a worksheet. Avoid using
RowSourceType 
Selected 
SpecialEffectSpecifies the visual appearance of an object. Can be any of the fmSpecialEffect constants.
TabIndex 
TabStop 
Tag 
TextChanges the selected row in a ListBox.
TextAlignSpecifies how text is aligned in a control. Can be any of the fmTextAlign constants.
TextColumnIdentifies the column in a ListBox to store in the Text property when the user selects a row.
TopIndex 
ValueSpecifies the state or content of a given control. This is the value in the BoundColumn.
Visible 
Width 


Methods

AddItem 
Clear 
Move 
RemoveItem 
SetFocus 
ZOrder 


Events

AfterUpdateFires each time the listbox selection changes. This does not fire unless the listbox has its Multi-Select set to Single.
BeforeDragOver 
BeforeDropOrPaste 
BeforeUpdate 
Change 
ClickThis does not fire unless the list box has its MultiSelect set to Single. In VB.NET this has been replaced with the SelectedIndexChanged and CheckStateChanged events.
DblClickIn VB.NET this has been replaced with the SelectionIndexChanged and TextChanged events.
Enter 
Error 
Exit 
KeyDown 
KeyPress 
KeyUp 
Layout 
MouseDown 
MouseMove 
MouseUp 
ValidateIn VB.NET this has been replaced with the Validating event.

© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited

PrevNext