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!

[drltr6]

Hi Ken. Is there a way of modifying the vba to allow the listbox selections to be placed in the order that they were selected. In other words, suppose I have a list of 10 players and I want the user to select 5 of them. I would like those 5 selected placed in the order in which they were selected, not in the order that they are listed in. Any possibility of this working?

[Ken Puls]

Sure, try this