MultiSelect Listbox

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

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
5
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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
 

drltr6

New member
Joined
Oct 25, 2011
Messages
22
Reaction score
0
Points
0
Excel Version(s)
MAC 2011
Hi Ken, I pasted the new code, but get an error message. Can you check it?
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
5
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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

drltr6

New member
Joined
Oct 25, 2011
Messages
22
Reaction score
0
Points
0
Excel Version(s)
MAC 2011
Works fine now. Thank you so very much. I'm excited to learn more vba.

Don
 

drltr6

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

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
5
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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)
 

drltr6

New member
Joined
Oct 25, 2011
Messages
22
Reaction score
0
Points
0
Excel Version(s)
MAC 2011
Thank you very much!
 

drltr6

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

drltr6

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

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
5
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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

drltr6

New member
Joined
Oct 25, 2011
Messages
22
Reaction score
0
Points
0
Excel Version(s)
MAC 2011
This time I get a Compile Error: Can't Assign to an array.
 

drltr6

New member
Joined
Oct 25, 2011
Messages
22
Reaction score
0
Points
0
Excel Version(s)
MAC 2011
ary()=

[h=2]Private subCmd()okay_Click[/h]
 
Last edited:

drltr6

New member
Joined
Oct 25, 2011
Messages
22
Reaction score
0
Points
0
Excel Version(s)
MAC 2011
Private Sub cmdOkay_Click()
 

drltr6

New member
Joined
Oct 25, 2011
Messages
22
Reaction score
0
Points
0
Excel Version(s)
MAC 2011
IT WORKS!!! Thank you so very much! I'm extremely happy right now!:love:
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
5
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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!)
 

drltr6

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