Using a Multiple-Selection List Box In an Excel Worksheet

The following code uses a command button in conjunction with a List Box to output all of the selected entries into the active worksheet.

1. Make sure the control toolbar has the "edit" button selected (the button to the far left on the control toolbar - the one with the ruler and pencil)
2. Create a list box in the worksheet.
3. Populate the list box using the following steps:
   
• Make sure that you are in control edit mode (click the symbol with the pencil and the ruler to the far left of the control toolbar- when edit mode is turned on, when you mouse over the list box, you will be able to click and drag the list box and should not be able to select any of the items within the list box)
• Right click the list box (while it is in edit mode)
• Click "properties"
• On the ListFillRange, type in the range which you want the list box to display. For example, If you want to type in the values you want displayed in the list box in Cells A1 through A10, type A1:A10
4. Right-click the list box and set the Name property to ListBox1 (it may already be set to this)
5. Set the MultiSelect property to 1 - fmMultiSelectMulti or 2 - fmMultiSelectExtended.
6. Create a command button that will be used as an "Enter" button that will be used to create an output from your list. Create this button next to your list box (the code below is specifically for a command button on the same page as the list box)
7. While the control tool bar is set to Edit mode, right-click the Command Button, click properties and make sure the name property is set to CommandButton1
8. Right-click the command button again and click View Code (or you can double-click the command button while in edit mode).
9. Now, while in VBA enter the following code so that your subroutine looks as follows:
   

Private Sub CommandButton1_Click()
Dim dblCounter As Double
Dim dblNumEntries As Double
Dim dblRowCount As Double
dblRowCount = 1
dblNumEntries = ListBox1.ListCount

'This code clears column 3 to get ready for list box output
For dblCounter = 1 To dblNumEntries
Application.ActiveSheet.Cells(dblCounter, 3).Value = ""
Next dblCounter

'This code adds the sleected list box output to column 3 (C)
For dblCounter = 0 To dblNumEntries - 1
If ListBox1.Selected(dblCounter) = True Then
Application.ActiveSheet.Cells(dblRowCount, 3).Value = ListBox1.List(dblCounter, 0)
dblRowCount = dblRowCount + 1
End If
Next dblCounter

End Sub

Note that this code will output the selected entries into the 3rd column (column C) of the active worksheet. You can easily change this by altering the "3" in this line of code:


Application.ActiveSheet.Cells(dblRowCount, 3).Value

You can also send the output else where by modify the Application.ActiveSheet portion of the code. For example if you want the output to go to "Sheet1" it would be coded as follows:


Worksheets("Sheet1").Cells(dblRowCount, 3).Value

If anyone has any questions about the analogous actions for a multi-column list box, feel free to post your question.

To use a list box in an Excel Worksheet that allows the user to make only one selection, implementation is much simpler, see:

Single Selection List Box in an Excel Worksheet