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.
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:
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.
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 the zipped sample Excel VBA Worksheet List Box file
For regular Excel news, tips, videos, and special offers, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.
Last updated: December 5, 2016 9:57 AM