Multi-select in listbox does not work.

Question

I have a macro that is supposed to populate a worksheet that is used by a listbox on a form. I set the properties of the listbox to allow multi selections. When the macro runs, the first selection is saved to the worksheet, then the rest of the selections are cleared from the list box. Therefore, the next selection(s) are not saved to the 'Style_List' worksheet. I am trying to figure out why. Here is the code. The listbox is cleared when the code reaches         Application.Worksheets("Style_List").Cells(slRow, 9).Value = Me.txtASIScore.Value.

' Copy data to Style_List worksheet

With sl

For i = 0 To Me.lstProductLineList.ListCount - 1

uid = Me.lstProductLineList.List(i, 1) & "-" & Me.lstProductLineList.List(i, 3)

slRow = Application.WorksheetFunction.Match(uid, Worksheets("Style_List").Range("A1:A100"), 0) 'Finds the associated row in the Style_List worksheet

    If Me.lstProductLineList.Selected(i) Then

        Application.Worksheets("Style_List").Cells(slRow, 9).Value = Me.txtASIScore.Value

        Application.Worksheets("Style_List").Cells(slRow, 10).Value = Me.cboSubmitStage.Value

        Application.Worksheets("Style_List").Cells(slRow, 11).Value = DateTime.Now

    End If

Next i

End With

I set the Row Source in the list box preperties. Would it be better if I out the Row Source in the code? If so, where in the code should the Row Source be specified?

Answer 1

I have come back to this to research it a bit more. The code you have, does it alter the values of the RowSource? I am assuming so and this will immediately clear the ListBox selections.

If you want to retain the selections then I suggest that instead of using RowSource, you copy the worksheet range to an array and then assign the array to the List property of the ListBox. The List property is actually an array itself and when the range is copied to an array and the array assigned to the List property then the List property is no longer connected to the worksheet and changing the worksheet values will not affect the selections.

However, if the cells in the worksheet are changed, do you then want the ListBox to reflect those changes. If so, re-run the code to copy the range to an array and reassign the array to the ListBox List property. This will also clear the selections so if you want to retain the selections you need to make a copy of the selections to an array as the first code to run and then after reassigning the array to  the Listbox, re-select with code.

The following code creates the array and assigns the array to the listbox from the form initialize event. Note that it calls another sub to do this so that same sub can be called again after changes to the worksheet range takes place. 

Hope you can follow this but feel free to get back to me if anything you do not understand.

Private Sub UserForm_Initialize()

    'Following call loads the array and subsequently _

     assigns the array to List property

    Call LoadList2List

End Sub

Sub LoadList2List()

    'This sub loads the worksheet range to an array _

     and assigns the array to the ListBox List property.

    Dim arrLstBox2List As Variant

    'Assign a range to an array.

    arrLstBox2List = Sheets("Sheet1").Range("A1:C10")

    'Next line ensures RowSource property is empty _

     otherwise assigning array to List property will error.

    ListBox2.RowSource = ""

    'Assign array to List property of ListBox

    ListBox2.List = arrLstBox2List

End Sub

Private Sub CommandButton1_Click()

    Dim arrSelected() As Variant

    Dim arrLstBox2List As Variant

    Dim i As Long

    Dim k As Long

    'Following loop saves the selected indexes to an array

    For i = 0 To ListBox2.ListCount - 1

        If ListBox2.Selected(i) Then

            k = k + 1

            ReDim Preserve arrSelected(1 To k)

            arrSelected(k) = i

        End If

    Next i

    'Changes the Source cells on the worksheet (For testing purposes)

    Sheets("Sheet1").Cells(3, "A") = "Changed"

    Call LoadList2List  'Reload the changed list to the ListBox

    'Following re-selects the same indexes of the ListBox

    For i = LBound(arrSelected) To UBound(arrSelected)

        ListBox2.Selected(arrSelected(i)) = True

    Next i

End Sub

Notes: You can also assign a worksheet range directly to the list property without the use of the intermediary array because the list property is an array in itself. See following example:-

    'Next line ensures RowSource property is empty _

     otherwise assigning array to List property will error.

    ListBox2.RowSource = ""

    ListBox2.List = Sheets("Sheet1").Range("A1:C10").Value

If you want to continue to use the RowSource, you could assign the selections to an array first and then process the selections by iterating through the array instead of the the selections.

Answer 2

It clears the selections.

Answer 3

I set up a test for this and it works fine. I don't suppost that you have some event code running for the worksheet that renews the list.

Added with Edit.

When you say "clears" do you mean clears the selections or clears the list (ie. no list to select from.)