ListBox Control in VBA
When you are working with Microsoft Office Excel VBA, if you want to make users to select values from only the list of available values you can use either ListBox control or ComboBox control. These both controls works in same way with some differences. Today we discuss about ListBox control in VBA.
ListBox control allows users to select single item or multiple items, by default it allows selecting single item only. We can add items to ListBox one by one or we assign excel Range to ListBox control in VBA.
First we discuss about how to add items to ListBox one by one. Open Microsoft Office 2013 Excel => Go to Developer Tab => Add ActiveX Command button and ListBox controls by using Insert Options as shown below.
Change Command button Caption to Add by right click on button, select properties and change Caption to Add as shown below.
Double Click the Add button, it generates click event. Add below code for the click event.
Private Sub CommandButton1_Click()
With Sheet1.ListBox1
.AddItem "John"
.AddItem "Raj"
.AddItem "Bob"
.AddItem "Smitha"
End With
End Sub
Now Unselect Design Mode to which besides after Insert button as shown below.
Click on Add button, it fills the ListBox with the values as shown below.
Even we can assign Excel Named range to ListBox, so that whenever new item added to range it automatically updates the ListBox.
First create Excel Named Range. Enter some values in first Column and select Formulas, Name Manager as shown below.
Create the new Name Manager by click on New button and enter values as shown below.
We provided name as EmpNames and Refers to value as Sheet1!$A:$A. Now got to the Developers tab, Insert one ActiveX ListBox control and provide EmpNames range to ListFillRange property as shown below.
Now the ListBox is automatically fills with EmpNames range. If you add any value to this Range, ListBox automatically updates with the EmpNames range.
By default we can select only single item, if you want to select multiple items we have to change MultiSelect property to 1 – frmMultiSelectMulti then we can select multiple values as shown below.
When Listbox is in single item selection mode, we can easily get the selected item by using Value property of ListBox as shown below.
Private Sub CommandButton1_Click()
MsgBox ListBox1.Value
End Sub
If ListBox is in Multi select mode, we have to loop through all items and have to check each item selected or not as shown below.
Private Sub CommandButton1_Click()
Dim sSelectedValues As String
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then
If sSelectedValues = "" Then
sSelectedValues = ListBox1.List(i)
Else
sSelectedValues = sSelectedValues & "," & ListBox1.List(i)
End If
End If
Next i
MsgBox sSelectedValues
End Sub
Unselect Design Mode, select couple of Items and click on the button. The output displays as shown below.
