As we saw in the Previous Article you learnt How to use Drop Down Box in Excel
 
If you want to read more about Excel Macro… visit Excel Macro Tutorial
 

In this Article you will learn How to Use ListBox in Excel. It will include
 
1. Adding Items in Excel ListBox
2. Removing Items from ListBox
3. Removing All items from ListBox
4. Selecting List Item from ListBox
5. Making ListBox as Check Box List (How to Check more than one Items in List Box)
6. Making ListBox as Radio Button List in Excel (How to Check Only one Item in List Box)
7. Excel ListBox Default Value
8. Get the Total Count of Items in a List Box
 
…..and many more things about Excel ListBox.

First will learn how to place ListBox in your Excel Sheet.
By going to Developer Tab –> Insert –> ListBox Activex Control

Excel Macro Tutorial – Add ListBox 1

Now Drag and Drop that Control any where you want in your Spreadsheet.
You can Also re-size the Height and Width etc keeping it in Design Mode

Excel Macro Tutorial – ListBox – Design Mode

Now as you can see that ListBox is created in the Sheet. Now you will learn How to Add Options or Items in the Drop down. As of Now it is completely blank. There is no option/item in it.
Basically there are two ways of Adding list Items in ListBox. 1. By Setting Range Formula in the Properties of ListBox.
 
2. By using Excel VBA Code

Add List Items in ListBox by Using Properties:

To Add items by setting Properties of the ListBox, follow the below Steps:
 
Step 1. Select the ListBox Control and Right Click and Open the Properties Window
Step 2. Now Enter the Cell Range in ListFillRange Property as shown below:

Excel Macro Tutorial – Add ListBox – Properties

Now whatever List Items you want to be there in the ListBox, Type them in that Range in Excel Sheet. All the list available in that Range will start appearing in the List Box as shown below:

Excel Macro Tutorial – Add ListBox 2

How to Make List of Check Boxes and Radio Buttons using this List Box

You might have in Visual Studio, we have separate Controls Like CheckBox List or RadioButton List Box.

How to Allow to Select More than One Item in List Box(List Of CheckBoxes):
To make this ListBox as a List of CheckBoxes, you need to change the below properties of the List Box:
1. ListStyle Property of the List Box to 0-fmListStyleOption.
2. MultiSelect Property of the ListBox set to 1 – fmMultiSelectMulti

Excel Macro Tutorial – Add Radio Button List Box

How to Allow to Select Only One Item from List Box (List Of Radio Buttons):

To make this ListBox as a List of CheckBoxes, you need to change the below properties of the List Box:
1. ListStyle Property of the List Box to 0-fmListStyleOption.
2. MultiSelect Property of the ListBox set to 1 – fmMultiSelectSingle

Excel Macro Tutorial – Add Check List Box

Add List Items in ListBox by Using Excel Macro (VBA Code):

In List Box, all the Items are by default Indexed from 0, 1, 2 etc. The first item which is added to ListBox will be indexed as 0, Second One will be Indexed as 1 and so on.. Therefore no need of any indexing here while adding list items in the ListBox.
To Add Item List in Drop Down List, there is a very Simple VBA Syntax as shown Below:
 
<ListBoxName>.AddItem <List Item> , <Index Number >
 

Sub Insert_Item_List()

Sheet1.ListBox1.AddItem "List 1", 0
Sheet1.ListBox1.AddItem "List 2", 1
Sheet1.ListBox1.AddItem "List 3", 2
Sheet1.ListBox1.AddItem "List 4", 3
Sheet1.ListBox1.AddItem "List 5", 4

End Sub

In the Above Code, Sheet1 is the Sheet Name where ListBox is there and ListBox1 is the Name of the ListBox Control.

Important:
In ListBox, when you are adding a New Item, It always gets added at the End of the List Which are already assigned to that Drop Down List. It means, It always appends the items to the list. So Every time you want a fresh List of Items in your ListBox, then before adding any new Item, Clear all the Existing Items from the ListBox. Below is the Code How to Clear All the Items from the ListBox
 

Most of the time you add List Items dynamically. For Example: you have some list of Values stored in an Array Variable and you want all of them to Add in the ListBox. In the below Code, you will learn how to Add List Items from an Array Variable using For loop.

Sub Insert_Item_List()

Dim iCounter As Integer
Dim Item(10) As String
'Store 10 List Items in an Array Variable
For i = 0 To 9
  Item(i) = "Option " & i + 1
Next
'Now Add all these items from Array Variable to ListBox
For i = 0 To 9
  Sheet1.ListBox1.AddItem Item(i)
Next
End Sub

Take the Above Code and Paste it in a Regular Module and Run it after adding a Listbox in your Sheet. It will Add 10 Items in the ListBox from “List 1” to “List 10”. But there is a problem in Above code. If you run the same Code twice, in your ListBox, there will be 20 list get added, if you run thrice then 30 and so on. Why? because as i told earlier that .AddItem always append the list. It does not clear the Previous ones and then add the new one. To overcome this Problem, we need to put a Statement to Clear all the Items before you add list in the Drop Down list item. Therefore in the below code, this problem will not occur.

Sub Insert_Item_List()

Dim iCounter As Integer
Dim Item(10) As String
'Store 10 List Items in an Array Variable
For i = 0 To 9
  Item(i) = "Option " & i + 1
Next
'Before Adding items to the List, Clear the List Box
  Sheet1.ListBox1.Clear
'Now Add all these items from Array Variable to ListBox
For i = 0 To 9
  Sheet1.ListBox1.AddItem Item(i)
Next
End Sub

As you saw how to Clear or Remove All the Items from the ListBox. Now you will learn how to remove a particular Item from ListBox

If you want to Remove a particular Item from the ListBox then use the below code to remove item from the ListBox. For Removing an Item, you need to pass Index Number as an Input Parameter.

 
<ListBoxName>.RemoveItem <Index Number >
 

Sheet1.ListBox1.RemoveItem 0

The Above Code will Always Remove the First List Item from the ListBox.

How to Select First List Item as by Default:

As you can see by default no List value is selected after adding items. If you want to make Some List Item as Default One then follow the below.
 
<ListBoxName>.ListIndex= <Index Number >
 
To select an Item from the ListBox you need to use the below Code
 

Sheet1.ListBox1.ListIndex = 0

 
In the Above example, First Item will be by default selected as I have used the Index Number as Zero (0). Similarly if you want to Select second or Third… give the Index Number of that Item.

Want to Select Blank Option in ListBox: To Select Blank Option from the ListBox, you should use Index Number as -1.
 

Sheet1.ListBox1.ListIndex = -1

If you want to Select the Last Item of the ListBox By default, then use the below Code:
 

Sheet1.ListBox1.ListIndex = Sheet1.ListBox1.ListCount - 1

How to Get Selected Value of the ListBox:

Below is the Simple Code which will show you how to get the Selected Value from the ListBox. .Value property of the Control ListBox1 returns the Selected Value of the ListBox.

MsgBox "Selected Value is" & ListBox1.Value

How to Get List of All Selected Items from the List Box?

.Value Property of the ListBox Control returns a Value, Only when the MultiSelect Property of the Control is Set to Single. If it is selected as Multiple then you need to Follow the below Code to get the List of All Selected Items from the ListBox.

Sub GetSelectedItems()
Dim SelectedItemArray() As String
ReDim SelectedItemArray(ListBox1.ListCount) As String
For i = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(i) = True Then
        SelectedItemArray(i) = ListBox1.List(i)
    End If
Next
End Sub

How to Get Total Count of Items in a List Box

.ListCount is a Property in Of ListBox object to get the Total Number of Items in a ListBox. .ListCount always returns a Number.
Refer the Below Code. It will give the the Total Number of Items in the ListBox1

MsgBox (ListBox1.ListCount)