Working with LIST BOXES (Form Controls)

This blog post shows you how to manipulate List Boxes (form controls) manually and with VBA code.

The list box shows you a number of values with a scroll bar (if needed).  The selected value is Asia and it is the fourth (4) value in list F1:F6. It links the relative position of selected value (4) in a list, here it is linked to cell D2.

Form controls are not as flexible as ActiveX controls but are compatible with earlier versions of Excel. You can find the controls on the developer tab.
How to enable developer tab: Excel 2007, Excel 2010 and Excel 2013

Table of Contents

1. Create a List Box
2. Assign macro to a List Box
3. Add values to a List Box
4. Link List Box to a cell
5. Delete values in a List Box
6. Set default value to a List Box
7. Allow multiple selected values in a List Box
8. Read selected values
9. Get Excel file

1. Create a List Box

Steps to build a List Box:

1. Go to tab "Developer" on the ribbon.
2. Press with left mouse button on "Insert" button.
   
3. Press with left mouse button on List Box button.
4. Press and hold and then drag with mouse on sheet.
   

You can build a List Box with vba code, here is how:

'Name macro
Sub Macro2()

'Insert Listbox to worksheet Sheet1 with dimensions Left=100, Top=50, Width=96.75, Height=32.25 and name it "List box 100"
'Worksheets("Sheet1").ListBoxes.Add(Left, Top, Width, Height)
Worksheets("Sheet1").ListBoxes.Add(100, 50, 96.75, 32.25).Name = "List box 100"
End Sub

Back to top

2. Assign macro to List Box

1. Press with right mouse button on with mouse on list box.
   
2. Press with left mouse button on "Assign macro...".
3. Select a macro in the list.
4. Press with left mouse button on OK.

You can assign macro to a List Box by using vba code.

Sub Macro3()
'Assign Macro2 to Listbox named List box 100
Worksheets("Sheet1").Shapes("List box 100").OnAction = "Macro2"
End Sub

Back to top

3. Add values to a List Box

You can add values to a List box with these steps:

1. Press with right mouse button on on List Box
2. Press with left mouse button on "Format Control..."
3. Go to tab "Control"
4. Select an Input range.
   
5. Press with left mouse button on OK

The following vba macro adds E1:E3 to the List Box input range.

Sub PopulateListBox2()

'Set input range for ListBox "List box 100" on worksheet Sheet1 equal to E1:E3
Worksheets("Sheet1").Shapes("List Box 100").ControlFormat.ListFillRange = _
"E1:E3"
End Sub

The VBA macro below adds the values in cell range E1:E3 to "List Box 100" without manipulating the input range. I recommend using the macro above, if values change in cell range E1:E3 they are instantly changed in List Box also. On the other hand, you can´t delete specific values using a VBA macro.

Sub PopulateListBox()
Worksheets("Sheet1").Shapes("List Box 100").ControlFormat.List = _
Worksheets("Sheet1").Range("E1:E3").Value
End Sub

If your cell range size often changes, consider using a named range. Use technique demonstrated here: Populate combox with values from a dynamic named range

You can also populate a List Box with excel table values:
Populate combox with values from a table

Back to top

4. Link List Box to a cell

1. Press with right mouse button on on List Box.
2. Press with left mouse button on "Format Control..."
3. Go to tab "Control"
4. Select a cell to link.
5. Press with left mouse button on OK

Sub LinkCell()
'Link cell A1 to LictBox "List box 100" loacted on worksheet Sheet1
Worksheets("Sheet1").Shapes("List box 100").ControlFormat.LinkedCell = "A1"
End Sub

Sub LinkCell2()
With Worksheets("Sheet1").Shapes("List box 100").ControlFormat
Worksheets("Sheet1").Range("B1").Value = .List(.ListIndex)
End With
End Sub

Back to top

5. Delete values in a List box

The following macro deletes all values in "List Box 100". It also removes the cell reference in "Input range:"

The RemoveItem method cannot be used to remove items from a list box if the list box is linked to a range on a worksheet.

You will get this error message: Run-time error '1004': RemoveItem method of ListBox class failed.

Sub RemoveAllItems()
Worksheets("Sheet1").Shapes("List Box 100").ControlFormat.RemoveAllItems
End Sub

This macro deletes the first value in "List Box 100".

Sub RemoveAllItems()
'Delete item in position 1 in ListBox List box 100 on worksheet Sheet1
Worksheets("Sheet1").Shapes("List box 100").ControlFormat.RemoveItem 1
End Sub

Back to top

6. Set default value in a List Box

This macro sets default value to the first value in a List Box.

Sub ChangeSelectedValue()
'Set item 1 the selected value in ListBox List box 100 on Sheet1
Worksheets("Sheet1").ListBoxes("List box 100").Selected = 1
End Sub

Back to top

7. Allow multiple selected values in a List Box

1. Press with right mouse button on on List Box.
2. Press with left mouse button on "Format Control...".
3. Go to tab Control.
4. Press with left mouse button on Multi in "Selection type" window.
   
5. Press with left mouse button on OK button.

This macro enables "Multiselection" for List Box 100.

Sub Allowmultiselect()
Worksheets("Sheet1").ListBoxes("List box 100").MultiSelect = xlSimple
End Sub

Back to top

8. Read selected value from Listbox

This macro returns the relative position of all selected values.

Sub ReadSelectedValue()

'With .. End With statement
With Worksheets("Sheet1").ListBoxes("List box 100")

'For ... Next statement
For i = 1 To .ListCount

'If ... Then statement, show a message box containing value in variable i if item i is the selected value
If .Selected(i) Then MsgBox i
Next i
End With
End Sub

If you prefer, you can return the selected value with .List(.ListIndex) property.

Sub ReadSelectedValue()
With Worksheets("Sheet1").ListBoxes("List box 100")
For i = 1 To .ListCount
If .Selected(i) Then MsgBox .List(.ListIndex)
Next i
End With
End Sub

Get the Excel file

Working-with-list-boxesv2.xlsm

---

Excel VBA Reference

This extensive vba reference list provides detailed information including syntax, arguments, return values, and examples for Visual Basic for Applications.