MultiSelect Listbox

D

drltr6

New member
Joined
Oct 25, 2011
Messages
22
Reaction score
0
Points
0
Excel Version(s)
MAC 2011
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.
 

Attachments

  • ListBoxMultiSelect (version 1).xls
    50 KB · Views: 680
Hi there, and welcome to the forum. :)

Try replacing your cmdOkay_Click routine with the following:

Code: 
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
 
Hi Ken, I pasted the new code, but get an error message. Can you check it?
 
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?
 

Attachments

  • xlgf539-1.xls
    52.5 KB · Views: 606
Works fine now. Thank you so very much. I'm excited to learn more vba.

Don
 
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?
 
Ugh, my bad. I missed something. Change the following line of code:
Code: 
i = Application.WorksheetFunction.Match(.Range("$B$10"), .Range("$G$2:$G$5"))

To this
Code: 
i = Application.WorksheetFunction.Match(.Range("$B$10"), .Range("$G$2:$G$5"), False)
 
Thank you very much!
 
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?
 
Thanks for the reply. When I try it, I get an error like this. Compile Error: Sub or Function Not Defined. With either the word "SPLIT" or "REPLACE" highlighted.
 
Ah right... forgot you're on a Mac.

I've added a mac compatible (conditionally complied) Substitute function, and replaced the REPLACE function with Application.Substitute instead. I think that should fix it up for you.
 

Attachments

  • xlgf539-3.xls
    59 KB · Views: 39
This time I get a Compile Error: Can't Assign to an array.
 
ary()=

[h=2]Private subCmd()okay_Click[/h]
 
Last edited:
Private Sub cmdOkay_Click()
 
IT WORKS!!! Thank you so very much! I'm extremely happy right now!:love:
 
Cool stuff, glad to help. (One day I'm going to need to get a Mac just so I can debug in the right platform!)
 
Hi Ken,
One more question. How can we alter the vba code to fix an error when the user clicks OK without making a selection? I plan on sending a donation for all of your help on this.
 

Attachments

  • xlgf539-4.xls
    66.5 KB · Views: 22
You must log in or register to reply here.
Back
Top