|
|
|
|
|
|
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
|
Option Explicit
Private Sub cmdCancel_Click()
Unload Me
End Sub
Private Sub cmdOkay_Click()
Dim i As Long, msg As String, Check As String
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
MsgBox "Nothing was selected! Please make a selection!"
Exit Sub
Else
Check = MsgBox("You selected:" & vbNewLine & msg & vbNewLine & _
"Are you happy with your selections?", _
vbYesNo + vbInformation, "Please confirm")
End If
If Check = vbYes Then
Unload Me
Else
For i = 0 To ListBox1.ListCount - 1
ListBox1.Selected(i) = False
Next
End If
End Sub
Private Sub UserForm_Initialize()
Dim cl As Range
With Me.ListBox1
.RowSource = ""
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
Option Explicit
Sub Launch()
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.
|
|