Excel Macro Tutorial : How to Use ListBox in Excel

.

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

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

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

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

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

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

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
 

 
Sheet1.ListBox1.Clear
 

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)

 
To Check out more Excel Macro Tutorials, visit Excel Macro Tutorial

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

4 Comments

  1. jose maria

    Hello, I`m interested excel’s macros

    Reply
  2. ganesh

    I checked nearly 20 sites/blogs/ boards for learning Listbox. This is the only forum to provide FULL information in a SIMPLE manner. All others merely copied/rephrased Microsoft Help file or gave the info in a even more convoluted way.
    My hearty appreciation and thanks.
    Ganesh

    Reply
  3. G S S prasad

    is it possible to add items in list box without using VBA

    Reply
  4. Steven Houghton

    I’ve added the listbox to my form and set the ListStyle to 1-fmListStyleOption so I can display the radio buttons in the form, but I can’t find out how to set if the button is checked or unchecked from the value of a cell.

    Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest