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:

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

Test the code:

  1. On Sheet1, enter some data in cells A10, A11, etc..., for as many rows as you'd like. (These items will fill your listbox)
  2. From the main Excel interface, press Alt + F8 to open the macro dialog box.
  3. Choose Launch and click Run.
  4. 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 422 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express