Working with LIST BOXES (Form Controls)

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

1. Introduction

What is a list box (Form Control)?

The list box shows you a number of values, with a scroll bar (if needed), from which the user can select.  This example demonstrated in the image above shows that 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. In other words, the list box is linked to Cell D2 and cell D2 shows 4 which means that the selected value is the fourth value in the list box.

Both the Form Control List box and ActiveX List Box allow multi-selection, however the ActiveX list box requires VBA to get it working properly. The list box (form control) can be populated using a predefined range of cells. It works well in Excel and earlier Excel versions without requiring additional coding meaning no VBA required.

What are the differences between form controls and ActiveX controls?

• Form controls are not as flexible as ActiveX controls but are compatible with earlier versions of Excel.
• Form controls work across different versions of Excel, including Mac unlike ActiveX controls which may not work on Mac.
• ActiveX controls support events, enhancing their functionality. In contrast, Form controls allow macros to be assigned which run when a user selects a value. While assigning a macro to a Form control is optional, it adds flexibility.

How to enable developer tab?

Here is how to enable the "Developer" tab if it is missing on the ribbon:

1. Press with left mouse button on "File" located above the ribbon, a new pane appears.
2. Press with mouse on the "Options" button to access Excel settings.
3. Press with mouse on "Customize Ribbon" and then on the right side press with left mouse button on the checkbox next to the tab "Developer" to enable it.
4. Press with left mouse button on "OK" button to apply changes.

The "Developer" tab is now visible on the ribbon. Here are instructions for Excel 2007, Excel 2010 and Excel 2013

What is the difference between a combo box and a list box?

A combo box requires less space on the worksheet but the user needs to interact with the combo box by press with left mouse button oning the down arrow to display the available items.

2. How to create a List Box manually and with VBA

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"
'Syntax: 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

3. Assign a given macro to List Box manually and programmatically

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. The name of the list box is required to properly assign the right one.

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

Back to top

4. How to add values to a List Box manually and programmatically

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

1. Press with right mouse button on on the 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()
'Programmatically add an input range to 
'a list box with the name List box 100

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

5. How to link a List box to a cell manually and programmatically

This example demonstrates  a macro that links a list box to a specific cell meaning when a user selects an item in the list box the linked cell shows the relative number of the selected item in that particular list.

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

The following macro shows how to link a List box to a given cell programmatically using VBA.

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

Sub LinkCell2()

'The WITH ... END WITH statement simplifies working with 
'a specific object by allowing you to run multiple 
'actions on it without repeatedly referencing the object. 
'This also makes the code shorter and easier to read.
With Worksheets("Sheet1").Shapes("List box 100").ControlFormat

'Retrieves the actual value (text) of the selected item and saves it to cell B1
Worksheets("Sheet1").Range("B1").Value = .List(.ListIndex)
End With
End Sub

Back to top

6. 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()

'Remove all items in list box 100 
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 relative position 1 in ListBox List box 100 on worksheet Sheet1
Worksheets("Sheet1").Shapes("List box 100").ControlFormat.RemoveItem 1
End Sub

Back to top

7. Set default value in a List Box

This macro sets default value to the first value in a List Box. The default value meaning which value is selected before user interaction.

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

8. 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

9. Read selected value from List box

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

Alternatively 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

10. Populate a list box with unique distinct values from a filtered Excel table - VBA

In this VBA tutorial I will show you how to populate a list box with unique distinct values from an Excel defined Table with a filter applied.

What is an Excel Table?

Excel defined Tables, introduced in Excel 2007, sort, filter and organize data any way you like. You can also format data and choose from many professional looking styles.

How to create an Excel Table?

1. Select the data set.
2. Go to tab "Insert" on the ribbon.
3. Press with left mouse button on "Table" button.
4. A dialog box appears.
5. Make the sure the cell range is correct.
6. Enable the check box if the data set has headers.
7. Press with left mouse button on the "OK" button.

Alternatively, press short cut keys CTRL + T to open the "Create Table" dialog box.

What are unique distinct values?

Unique distinct values are all values except duplicates. Duplicates are merged into one distinct value meaning there is only one instance of each value.

Unique distinct values are great in a List box. This reduces the number of items making it easier to use.

Instructions

1. Sort or/and filter the table.
2. Press the "Populate list box" button.

Only unique distinct visible values from Column1 are added to the list box. Hidden values are filtered out when you apply a filter to the Excel Table.

VBA code

'Name macro
Sub FilterUniqueData()

'Declare variables and data types
 Dim Lrow As Long, test As New Collection, i As Single
 Dim Value As Variant, temp As Range

'Ignore errors in macro and continue with next line
On Error Resume Next

'Save values from first column in Table1 to temp object
Set temp = Worksheets("Sheet1").ListObjects("Table1").ListColumns(1).Range

'Iterate through values in first column except header value
For i = 2 To temp.Cells.Rows.Count
    'Save value to test if the number of characters are more than 0 and the row is not hidden
    If Len(temp.Cells(i)) > 0 And Not temp.Cells(i).EntireRow.Hidden Then
        test.Add temp.Cells(i), CStr(temp.Cells(i))
    End If

'Continue with next value
Next i

'Delete all items in listbox
Worksheets("Sheet1").Shapes("List Box 1").ControlFormat.RemoveAllItems

'Iterate through all values saved to test
For Each Value In test

   'Add value to listbox
   Worksheets("Sheet1").Shapes("List Box 1").ControlFormat.AddItem Value

'Continue with next value
Next Value

'Delete object test
Set test = Nothing

End Sub

10.1 Where do I put the code in my workbook?

1. Start the Visual Basic Editor (Alt+F11).
2. Press with left mouse button on "Insert" on the top menu, see image above.
3. Press with left mouse button on "Module".
4. Paste code to window.
5. Return to Excel.

Back to top

Get the Excel file

Excel-table-List-box.xlsm

LISTBOXES examples

Working with LIST BOXES (Form Controls)
This blog post shows you how to manipulate List Boxes (form controls) manually and with VBA code. Table of Contents […]

SHAPES examples

Add checkboxes and copy values – VBA
Table of Contents Add Checkboxes to a Worksheet Copy selected rows based on check boxes Multi-level To-Do list template 1. […]

Change chart data range using a Drop Down List [VBA]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]

Change PivotTable data source using a drop-down list
In this article, I am going to show you how to quickly change Pivot Table data source using a drop-down […]

How to change a picture in a worksheet dynamically [VBA]
Rahul asks: I want to know how to create a vlookup sheet, and when we enter a name in a […]

How to create an interactive Excel chart
Table of Contents How to create an interactive Excel chart How to filter chart data How to build an interactive […]

How to use the Scroll Bar
This article demonstrates how to insert and use a scroll bar (Form Control) in Excel. It allows the user to […]

List all hyperlinks in worksheet programmatically
Table of Contents List all hyperlinks in worksheet programmatically Find cells containing formulas with literal (hardcoded) values Extract cell references […]

Locate a shape in a workbook
This article demonstrates how to locate a shape in Excel programmatically based on the value stored in the shape. The […]

Move a shape [VBA]
This article demonstrates how to move a shape, a black arrow in this case, however, you can use whatever shape […]

Multi-level To-Do list template
Today I will share a To-do list excel template with you. You can add text to the sheet and an […]

Populate a combo box (form control) [VBA]
In this tutorial I am going to explain how to: Create a combo box (form control) Filter unique values and […]

Populate a list box with unique distinct values from a filtered Excel table [VBA]
Excel defined Tables, introduced in Excel 2007, sort, filter and organize data any way you like. You can also format […]

Run a Macro from a Drop Down list [VBA]
This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]

Show / hide a picture using a button
This article explains how to hide a specific image in Excel using a shape as a button. If the user […]

Toggle a macro on/off using a button
This article demonstrates how the user can run a macro by press with left mouse button oning on a button, […]

Working with COMBO BOXES [Form Controls]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]

Working with LIST BOXES (Form Controls)
This blog post shows you how to manipulate List Boxes (form controls) manually and with VBA code. Table of Contents […]

Working with TEXT BOXES [Form Controls]
There are two different kinds of text boxes, Form controls and ActiveX Controls. Form controls can only be used on […]

Excel VBA Reference

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

Form controls category

Working with COMBO BOXES [Form Controls]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]

Create a Print button – macro
This article describes how to create a button and place it on an Excel worksheet, then assign a macro to […]

Working with TEXT BOXES [Form Controls]
There are two different kinds of text boxes, Form controls and ActiveX Controls. Form controls can only be used on […]

Listbox category

Excel categories

Comments (11)