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 is VBA?

VBA (Visual Basic for Applications) is a programming language developed by Microsoft that is used for automating tasks in Microsoft Office applications such as Excel, Word, and Access. It allows users to create macros, automate repetitive tasks, and enhance Office functionality with custom scripts.

What is the Visual Basic Editor (VBE)?

The Visual Basic Editor (VBE) is an integrated development environment (IDE) within Microsoft Office applications (such as Excel, Word, and Access) that allows users to write, edit, and debug Visual Basic for Applications (VBA) code.

How to open the Visual Basic Editor?

You can open the Visual Basic Editor (VBE) using any of the following methods:

• Method 1: Using Keyboard Shortcut
  • Press Alt + F11 on your keyboard.
  • The Visual Basic for Applications (VBA) Editor window will open.
• Method 2: Using the Developer Tab
  • Press with mouse on the Developer tab in the ribbon.
  • Press with left mouse button on Visual Basic to open the VBE.

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

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.
   

3. How to create a List Box with VBA

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

 

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

5. Assign a given macro to List Box programmatically

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

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

7. How to add values to a List Box programmatically

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

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

9. How to link a List box to a cell programmatically

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

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

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

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

13. Allow multiple selected values in a List Box programmatically

This macro enables "Multiselection" for List Box 100.

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

Back to top

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

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

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

Form controls category

Listbox category

More than 1300 Excel formulas

Excel categories

Comments (11)