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). 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
- Create a List Box
- Assign macro to a List Box
- Add values to a List Box
- Link List Box to a cell
- Delete values in a List Box
- Set default value to a List Box
- Allow multiple selected values in a List Box
- Read selected values
Create a List Box
Steps to build a List Box:
- Go to tab "Developer" on the ribbon.
- Click "Insert" button.
- Click List Box button.
- Click and hold and then drag with mouse on sheet.
You can build a List Box with vba code, here is how:
Sub Macro2() 'Worksheets("Sheet1").ListBoxes.Add(Left, Top, Width, Height) Worksheets("Sheet1").ListBoxes.Add(100, 50, 96.75, 32.25).Name = "List box 100" End Sub
Assign macro to List Box
You can assign macro to a List Box by using vba code.
Sub Macro3() Worksheets("Sheet1").Shapes("List box 100").OnAction = "Macro2" End Sub
Add values to a List Box
You can add values to a List box with these steps:
- Right click on List Box
- Click "Format Control..."
- Go to tab "Control"
- Select an Input range.
- Click OK
The following vba macro adds E1:E3 to the List Box input range.
Sub PopulateListBox2() 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
Link List Box to a cell
- Right click on List Box
- Click "Format Control..."
- Go to tab "Control"
- Select a cell to link.
pic - Click OK
Sub LinkCell() 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
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 messages: 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 first value in "List Box 100".
Sub RemoveAllItems() Worksheets("Sheet1").Shapes("List box 100").ControlFormat.RemoveItem 1 End Sub
Set default value in a List Box
This macro sets default value to the first value in a List Box.
Sub ChangeSelectedValue() Worksheets("Sheet1").ListBoxes("List box 100").Selected = 1 End Sub
Allow multiple selected values in a List Box
- Right click on List Box.
- Click "Format Control...".
- Go to tab Control.
- Click Multi in "Selection type" window.
- Click OK button.
This macro enables "Multi" for List Box 100.
Sub Allowmultiselect() Worksheets("Sheet1").ListBoxes("List box 100").MultiSelect = xlSimple End Sub
Read selected value(s)
This macro returns the relative position of all selected values.
Sub ReadSelectedValue() With Worksheets("Sheet1").ListBoxes("List box 100") For i = 1 To .ListCount 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
Download excel *.xlsm file
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 […]
This article describes how to create a button and place it on an Excel worksheet then assign a macro to […]
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 […]
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 […]
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 […]
Populate listbox with unique distinct values [VBA]
This post demonstrates how to: Insert a button to your worksheet Assign a macro to the button Create a basic […]
4 Responses to “Working with LIST BOXES (Form Controls)”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
[…] Working with List Boxes (Form Controls) […]
hi thanks for the tutorial
when i executed your readselectedvalue macro, excel debugged here:
MsgBox .List(.ListIndex)
Any way to scroll via vba?
I use as a console output but have to display messages bottom-to-top since I can't auto-scroll to the last row of the listbox.
Hello, How do you link a List Box or Combo box to a Text box?
For example, I am trying to set a specific value in the listbox, which is "Others" and when the user selects that, it moves over (activates) to the Text box.