MultiSelect Listbox

[drltr6]

I am new to VBA and need some guidance and help. I am trying to modify the vba in the attached example. I would like to take the selections in a userform to populate other cells. I have used Index/Match to select a school name and place the 10 individual players from that school into a userform which allows for multiple selections from the list. I would like to have those selections (up to 6 players) placed in other cells based on the school selected. Is this possible? Any help would be greatly appreciated.

[Ken Puls]

Hi there, and welcome to the forum. :)

Try replacing your cmdOkay_Click routine with the following:


Private Sub cmdOkay_Click()
Dim i As Long, j As Long, msg As String, Check As String, ary(5) As Variant
'Generate a list of the selected items
With ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
msg = msg & .List(i) & vbNewLine
ary(j) = .List(i)
j = j + 1
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 select a minimum of 4 Players!"
Exit Sub
Else
'Ask the user if they are happy with their selection(s)
Check = MsgBox("You selected:" & vbNewLine & msg & vbNewLine & _
"Are these selections correct?", _
vbYesNo + vbInformation, "Please confirm")
End If
If Check = vbYes Then
'Determine row in which to place values
With ActiveSheet
i = Application.WorksheetFunction.Match(.Range("$B$10"), .Range("$G$2:$G$5"))
With .Range("$H$1:$M$1")
.ClearContents
.Offset(i, 0).Value = ary()
End With
End With
'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

[drltr6]

Hi Ken, I pasted the new code, but get an error message. Can you check it?

[Ken Puls]

Hi there,

I've pasted it directly into your workbook (in the userform) and it seems to be working fine for me here.

Can you tell me what the error is, and what version of Excel you're on?

[drltr6]

Works fine now. Thank you so very much. I'm excited to learn more vba.

Don

[drltr6]

Hi Ken, Sorry to bother you, but I am still getting errors. If I choose Jenison as the team, and then click to select players, the results go to the Grandville list. If I then choose the Grandville team, I get a run error. I am using Office for Mac 2004. Any ideas?

[Ken Puls]

Ugh, my bad. I missed something. Change the following line of code:

i = Application.WorksheetFunction.Match(.Range("$B$10"), .Range("$G$2:$G$5"))

To this

i = Application.WorksheetFunction.Match(.Range("$B$10"), .Range("$G$2:$G$5"), False)

[drltr6]

Thank you very much!