Search Contextures Sites

 

Contextures
Excel news
by email

We respect your email privacy

 

Your worksheet formulas can create traffic-light charts, highlight chart elements, assign number formats, and more.

 

 

 

 

 

Learn how to create Excel dashboards.

 

 

 

30 Excel Functions in 30 Days

 

 

 

Learn how to create Excel dashboards.

 

 

 

Learn how to create Excel dashboards.

 

 

Live-link your Excel dashboards to nearly any web data.

 

 

 

Learn how to create Excel dashboards.

 

 

 

Learn how to create Excel dashboards.

 

 

Excel VBA - Create a Worksheet List Box

Create a List Box on the Worksheet  
Extract the Selected List Box Items  
Excel VBA Code to Extract List Box Items  
Download a Sample Excel VBA List Box file  

Watch the Excel VBA List Box video

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.

ListBox 01

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.

    ListBox 01

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

Download a Sample Excel VBA List Box file

Download the zipped sample Excel VBA Worksheet List Box file

Watch the Worksheet List Box Video

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

 

Privacy Policy

 

Contextures Inc., Copyright ©2013
All rights reserved.

 

Last updated: May 29, 2013 3:14 PM