Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 25

Thread: MultiSelect Listbox

  1. #1

    MultiSelect Listbox



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.
    Attached Files Attached Files

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,195
    Articles
    57
    Blog Entries
    14
    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
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

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

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,195
    Articles
    57
    Blog Entries
    14
    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?
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

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

    Don

  6. #6
    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?

  7. #7
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,195
    Articles
    57
    Blog Entries
    14
    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)
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  8. #8
    Thank you very much!

  9. #9
    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?

  10. #10
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,195
    Articles
    57
    Blog Entries
    14
    Sure, try this
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Page 1 of 3 1 2 3 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •