VBA Express : Excel - Identify Selected Item in a multi selection Listbox

Excel

Identify Selected Item in a multi selection Listbox

Ease of Use

Intermediate

Version tested with

2003

Submitted by:

Ken Puls

Description:

This is very simple example of how to set up a listbox to allow multiple selections, verify that at least one item in a userform listbox has been selected, and identify which items were chosen. While it will also work with a single selection listbox, more efficient code for a single selection listbox can be found at http://www.vbaexpress.com/kb/getarticle.php?kb_id=303.

Discussion:

When using a listbox on a userform, it is sometimes desireable to be able to let the user pick multiple items at one time. When allowing this, it is then important to ensure that your user has picked at least one item, and then identify what the chosen items were. This is a very simple illustration of how to do both. The listbox in this example is filled from a worksheet range, but it can easily be filled using code (as shown in the KB article link above.) Finally, this code also shows how to return the user to the useform if the selections did not satisfy the user and they want to try again.

Code:

instructions for use

			 '** The following code goes in a userform **
Option Explicit 
 
Private Sub cmdCancel_Click() 
     'Unload the userform
    Unload Me 
End Sub 
 
Private Sub cmdOkay_Click() 
    Dim i As Long, msg As String, Check As String 
     
     'Generate a list of the selected items
    With ListBox1 
        For i = 0 To .ListCount - 1 
            If .Selected(i) Then 
                msg = msg & .List(i) & vbNewLine 
            End If 
        Next i 
    End With 
     
    If msg = vbNullString Then 
         'If nothing was selected, tell user and let them try again
        MsgBox "Nothing was selected!  Please make a selection!" 
        Exit Sub 
    Else 
         'Ask the user if they are happy with their selection(s)
        Check = MsgBox("You selected:" & vbNewLine & msg & vbNewLine & _ 
        "Are you happy with your selections?", _ 
        vbYesNo + vbInformation, "Please confirm") 
    End If 
     
    If Check = vbYes Then 
         'Unload the userform since user is happy with selection(s)
        Unload Me 
    Else 
         'User wants to try again, so clear listbox selections and
         'return user to the userform
        For i = 0 To ListBox1.ListCount - 1 
            ListBox1.Selected(i) = False 
        Next 
    End If 
     
End Sub 
 
Private Sub UserForm_Initialize() 
    Dim cl As Range 
     
     'Load the combobox with a variety of household pets
    With Me.ListBox1 
         'Clear the rowsource in case it has been set
        .RowSource = "" 
         
         'Add the items from Cell A10 to the last used row in column A
        For Each cl In Worksheets("Sheet1").Range("A10:A" & _ 
            Worksheets("Sheet1").Range("A65536").End(xlUp).Row) 
            .AddItem cl.Value 
        Next cl 
    End With 
End Sub 
 
 '** The following code goes in a standard module **
Option Explicit 
 
Sub Launch() 
     'This code will launch the userform
    UserForm1.Show 
End Sub

How to use:

Open the VBE (press Alt+F11).
If the project explorer is not open, press CTRL + R to open it.
Navigate to the desired file, right click it and choose "Insert UserForm".
Double click the userform in the project explorer.
If the "Toolbox" toolbar is not open, open it from the View menu.
Add a listbox and two commandbuttons to the userform.
Right click the listbox and choose "Properties".
In the window that pops up, change the MultiSelect property to "1-fmMultiSelectMulti".
Right click one of the commandbuttons and choose "Properties".
In the window that pops up, change the (Name) property to cmdOkay, and the Caption variable to Okay.
Using the same method, change the second button's (name) to cmdCancel, and its Caption to Cancel.
Copy the userform subroutines from above (everything up to the line that denotes the Standard module code).
Right click the UserForm in the project explorer and choose "View Code".
Paste the code in the right pane.
Copy the last procedure from above (Sub Launch).
In the project explorer, right click the project name again and choose Insert -> Module.
Paste the code in the right pane.
Press Alt + Q to close the VBE.
Save workbook before any other changes.

Test the code:

On Sheet1, enter some data in cells A10, A11, etc..., for as many rows as you'd like. (These items will fill your listbox)
From the main Excel interface, press Alt + F8 to open the macro dialog box.
Choose Launch and click Run.
Try clicking okay without anything selected, and then try after selecting one or more items.

Sample File:

ListBoxMultiSelect.zip 12.53KB

Approved by mdmackillop

This entry has been viewed 440 times.