How to retain the checkbox selections in a MultiSelection Listbox?

Question

I have a multiselection listbox with the option style (check boxes to the left).

I have the below code to transfer the selected data to the worksheet (this must be dynamic since the listbox itself is fed by a dynamic named range).

Private Sub cmdRun_Click()

Dim i As Integer

i = 0

Do While i < lstProperties.ListCount + 1
    If lstProperties.Selected(i) = True Then
    Sheet7.Cells(i + 1, 1) = lstProperties.List(i)
    End If
    i = i + 1
Loop

End Sub

It seems like it wants to work but I think when I run the command it is unselecting the list box data after the first loop and therefore only transposing the first selection in the list box.

Answer 1

I realize this is an old thread, but it’s the only one I could find on the topic, and it was never solved.

I was having the exact same problem with my userform (only the first selected item in the list is returned to the worksheet, as it appears to deselect the listbox data before looping through the remaining items), but after reading this thread I was able to come up with a solution.

The problem is that the RowSource is a dynamic named range (“FilterData”), and when the first selected item is returned to the worksheet it triggers the workbook to recalculate all formulas, including the formula for your dynamic named range, thus refreshing the listbox RowSource and clearing the selected items before it has a chance to loop through them all.

The solution is to turn off automatic formula calculations before your Do While Loop statement (and turn it back on afterwards... very important) like so:

Application.Calculation = xlCalculateManual
Do While i < lstProperties.ListCount
    If lstProperties.Selected(i) = True Then
        Sheet7.Cells(Rw, 1) = lstProperties.List(i)
        Rw = Rw + 1
    End If
    i = i + 1
Loop
Application.Calculation = xlCalculateAutomatic

Hopefully that helps anyone else looking for a solution to the same problem.

Cheers!