ListBox
By Ajeet Kumar
By Ajeet Kumar
A ListBox is a userform control
which shows a list of data items. The list can be one column data items or it
can be a list of multi-column data items. A single column list can be
considered as one-dimensional array and multi-column list as
two-dimensional array. (List and Array are not same data structures) A ListBox allows users to select one or more rows of
data in contrast to ComboBox which allows selection of single row from the
drop-down rows. The items in a list box can appear as plain text or they can
appear with option buttons or check boxes in front of each item. In case of
option button, the user can select only one item from the list of the list box.
In case of check boxed items, the user can select more than one items.
Plain Style List Box with
single row selection feature: The below screenshot with its code allows
user to select only one row at a time. So, in this case, data selection is
basically from mutually exclusive items.
Private Sub UserForm_Initialize()
Dim vData As Variant
With Me.ListBox1
vData = Sheet1.Range("A2:A"
& Sheet1.Range("A1").CurrentRegion.Rows.Count - 1)
.List = vData
.ListStyle = fmListStylePlain
.ColumnHeads = False
.MultiSelect = fmMultiSelectSingle ‘’ select a single row or data item
End With
End Sub
Plain Style List Box with
Multiple Selection Feature
Private Sub UserForm_Initialize()
Dim vData As Variant
With Me.ListBox1
vData = Sheet1.Range("A2:A"
& Sheet1.Range("A1").CurrentRegion.Rows.Count - 1)
.List = vData
.ListStyle = fmListStylePlain
.ColumnHeads = False
.MultiSelect = fmMultiSelectMulti ‘’ select multiple rows of the list
End With
End Sub
Single-column ListBox
Private Sub UserForm_Initialize()
With Me.ListBox1
vData = Sheet1.Range("A2:A"
& Sheet1.Range("A1").CurrentRegion.Rows.Count - 1)
.List = vData
.ListStyle = fmListStyleOption
.MultiSelect = fmMultiSelectSingle
End With
End Sub
Multi-column ListBox
Private Sub UserForm_Initialize()
Dim vData As Variant
With Me.ListBox1
vData = Sheet1.Range("A2:A"
& Sheet1.Range("A1").CurrentRegion.Rows.Count - 1)
.List = vData
.ListStyle = fmListStyleOption
.MultiSelect = fmMultiSelectMulti
End With
End Sub
-------------------------------------------------------
List all selected Items of the
list box
Private Sub CommandButton1_Click()
'' List all selected items
Dim x As Integer
Dim Str As String
Str = vbNullString
For x = 0 To ListBox1.ListCount - 1
'' Selected property is valid for ListBox and not for ComboBox
If ListBox1.Selected(x) Then
Str = Str & ", "
& ListBox1.List(x)
End If
Next
MsgBox Right(Str, Len(Str) - 1)
End Sub
Column Count Property
ColumnWidths Property
ColumnWidths Property
ColumnCount =0 it is used to
suppress all the columns of the ListBox.
ColumnCount =-1 is used to display
all the columns of the ListBox
The number of columns is a ListBox
is called column count and each column of the ListBox can be given a definite
width by the developer by the help of column Widths property. For example, if
the column Count is 3 then ColumnWidths can be 55;85;74. In this example the
width of 1st column is 55, of 2nd column is 85 and of 3rd
column is 74. The column widths of the ListBox cannot be more than its number
of column count. Although, we can specify the column widths for less than the column
count, If we specify the column widths for 4th column while the column
Count is 3 we get type mismatch error as depicted below:
Column widths must be written as
double-quoted string and the widths be separated by semi-column. If it is colon
by spelling error, run time error will be.
AddItem Method of ListBox
The AddItem method of list box is
used to programmatically add items in the list at run time. So, it provides
dynamic approach to add items in the list.
The AddItem method cannot be used
if the RowSource property of the list box is not ZLS. So, whenever you want to
use AddItem method, set the RowSource=”” to avoid error. In other words, the listbox
control must be unbound control to use AddItem method. AddItem method has 2 parameters called item
and index. The item is the value of item which is to be added to the list and
index is the position in the list. If the index parameter is omitted, the item
is added to the next row.
Let's consider the following procedures for the buttons shown in the below picture:
Add Items Button Procedure
Private Sub CommandButton1_Click()
With ListBox1
. RowSource = ""
. AddItem "JAN"
.AddItem "FEB"
.AddItem "MAR"
.AddItem "APR"
.AddItem "MAY"
.AddItem "JUN"
.AddItem "JUL"
.AddItem "AUG"
.AddItem "SEP"
End With
End Sub
Remove List Button Procedure
Private Sub CommandButton2_Click()
If ListBox1.ListCount > 0 Then
ListBox1.RemoveItem 0
Else
MsgBox "No item found in the
listbox", vbOKOnly + vbInformation, "List Items"
End If
End Sub
Select & Remove Button
Procedure
Private Sub CommandButton3_Click()
''ListIndex returns the index of selected item in list box
ListBox1.RemoveItem ListBox1.ListIndex
End Sub
ListBox List Style (
fmListStylePlain )
ListBox List Style ( fmListStyleOption )
When ListBox1.ListStyle = fmListStyleOption,
we get the option buttons in the ListBox provided we don’t use the MultiSelect
property of the ListBox with value not equal to fmMultiSelectSingle.
ListBox with check boxes:
ListBox1.ListStyle =
fmListStyleOption and
ListBox1.MultiSelect =
fmMultiSelectMulti
Text Vs Value Property of
ListBox
Private Sub CommandButton1_Click()
''Text property refers to the Text of the TextColumn ListBox
MsgBox ListBox1.Text, vbOKOnly + vbInformation, "Text Property"
''Value property refers to the Value of the BoundColumn ListBox
MsgBox ListBox1.Value, vbOKOnly + vbInformation, "Value
Property"
End Sub
Private Sub UserForm_Initialize()
ListBox1.RowSource = Sheet1.Range("A2:C" &
Sheet1.Range("A1").CurrentRegion.Rows.Count).Address
'' Control Source cell stores the value as per BoundColumn
ListBox1.ControlSource = Sheet1.Range("E1")
ListBox1.ColumnCount = 3
ListBox1.BoundColumn = 2
ListBox1.TextColumn = 1 '' 3 will be for date of birth
End Sub
Selected Property
If list box has multiple selection enabled, you cannot use click event to show any message etc. The reason is very simple and intuitive. The VBA doesn't know for which one list item, it should raise the event. Therefore, don't use any such kind of coding if your list box has multiple selection allowed.
If list box has multiple selection enabled, you cannot use click event to show any message etc. The reason is very simple and intuitive. The VBA doesn't know for which one list item, it should raise the event. Therefore, don't use any such kind of coding if your list box has multiple selection allowed.
List box has its unique property called Selected. This property is not present for ComboBox control. This property returns a boolean value. You can pass list index as argument to Selected property.
For example, ListBox1.Selected(0) refers to the boolean state of first row selection. If the first row is selected, it will return true else it'll return false.
List Property
For example, ListBox1.Selected(0) refers to the boolean state of first row selection. If the first row is selected, it will return true else it'll return false.
List Property
List box List property is a read write property which can be used to read all items of the list. We can directly pass a complete list to the list box using list property. The data passed should be an array. The array can be one or two dimensional.
To pass an individual data item to the list, use row and column arguments of the List.
List box.List(rowindex, column index)
The rowindex and column index starts from zero.
Column Property
ListCount Property
Column Property
ListCount Property
ListCount property returns the total number of rows present in the ListBox.
ColumnCount property returns the total number of columns present in the ListBox.
The default value of ColumnCount is 1.
BoundColumn is an important concept with respect to ListBox and ComboBox.
The values of BoundColumn can be -1 to any positive integer.
ListBox Events
List box has a number of events such as change, click, afterupdate etc.
No comments:
Post a Comment