Home > Macros > Basics > List Box Create a Worksheet List Box To make data entry easier, create lists with the List Box control. Check one or more of the items, and add the selected items to the worksheet.

Create a List Box on the Worksheet

In an Excel worksheet, you can create lists by using the List Box control. In this example, the worksheet has a List Box control that allows multiple items to be selected, and there is a check box at the left of each item.

This ListBox shows the entries from a named range -- DaysList, that lists the five weekdays.

To create the ListBox on the worksheet, follow these steps:

1. On the Excel Ribbon, click the Developer tab
2. Click Insert, then click the ListBox control tool

   

3. On the worksheet, draw a rectangle with the List Box tool, to create the List Box.
4. With the List Box selected on teh worksheet, click the Properties command on the Ribbon's Developer tab. The Properties window will open.

   

5. In the Properties window, for the ListFillRange setting, enter DaysList -- the named range with the weekdays.
6. Click in the MultiSelect property, and from the drop down list, select 1-fmMultiSelectMulti

   

7. Click in the ListStyle property, and from the drop down list, select 1-fmListStyleOption. This adds check boxes to the left of the list items.

   

8. Close the Properties window.
9. On the Ribbon's Developer tab, click the Design Mode setting, to turn it off.
10. In the List Box, click one or more weekdays, to select them.

    

Extract the Selected List Box Items

If items have been selected in a List Box, you can use Excel VBA code to extract the selected items, and copy them to cells in the workbook.

In the following example, Volunteer information is being collected. The name and city for each user is entered on the worksheet. In the List Box, the weekdays that the volunteer is available are checked.

To add the new information to the volunteer database, click the Add button

The volunteer's Name and City are copied to the database sheet, and the current date and user name are entered in columns A and B.

The days selected in the List Box are entered in the same row, starting in column E.

Excel VBA Code to Extract List Box Items

Here's the completed Excel VBA code for the Volunteer Registration data entry form. It copies the data to the database sheet, then clears the data entry cells, and the List Box.

Sub AddLogRecord()

Dim historyWks As Worksheet
Dim inputWks As Worksheet

Dim nextRow As Long
Dim oCol As Long

Dim myCopy As Range
Dim myTest As Range

Dim LB As MSForms.ListBox
Dim lCount As Long

Set inputWks = Worksheets("DataEntry")
Set historyWks = _
  Worksheets("VolunteerList")

Set LB = inputWks _
  .OLEObjects("LB_Days").Object
'cells to copy from Input sheet
Set myCopy = inputWks.Range("VolInfo")

oCol = 3 'starting column
'where copied data is pasted

With historyWks
  nextRow = .Cells(.Rows.Count, "A") _
    .End(xlUp).Offset(1, 0).Row
End With

With inputWks
  If Application.CountA(myCopy) _
    <> myCopy.Cells.Count Then
    MsgBox "Please fill in all the cells!"
    Exit Sub
  End If
End With

With historyWks
  With .Cells(nextRow, "A")
    .Value = Now
    .NumberFormat = "mm/dd/yyyy hh:mm:ss"
  End With
  .Cells(nextRow, "B").Value = _
    Application.UserName
  myCopy.Copy
  .Cells(nextRow, oCol).PasteSpecial _
    Paste:=xlPasteValues, _
    Transpose:=True
  Application.CutCopyMode = False
End With

oCol = oCol + myCopy.Cells.Count

'------------
'get list data

With LB
  For lCount = 0 To .ListCount - 1
    If .Selected(lCount) Then
      historyWks.Cells(nextRow, _
        oCol).Value = .List(lCount)
      oCol = oCol + 1
    End If
  Next
End With
'------------

'clear input cells
With myCopy
  .Cells.ClearContents
  .Cells(1).Select
End With

'clear Days list
DayListClear

End Sub

'==================================

Sub DayListClear()

Dim ws As Worksheet
Dim LB As MSForms.ListBox
Dim lCount As Long

Set ws = ActiveSheet
Set LB = _
  ws.OLEObjects("LB_Days").Object

With LB
    For lCount = 0 To .ListCount - 1
       .Selected(lCount) = False
    Next
End With

End Sub

Video: Create a Worksheet List Box

To see the steps for creating a List Box on the worksheet, please watch this short Excel video tutorial.

Get a Sample Excel VBA List Box file

Get the zipped sample Excel VBA Worksheet List Box file

More Tutorials

Multi Select Listbox

Move ListBox Items

Move ListBox Items on UserForm