Persisting content of MSForms ListBox with ListStyle = fmListStyleOption

Question

I have created a UserForm in VBA Excel that has a ListBox with the ListStyleOption selected. The MultiSelectMulti option is activated. Whenever I close the UserForm or Workbook and then reopen, all the previous selections are gone. Is there a way to retain selections made in listbox?

Thanks.

Answer 1

Yes, it's possible, but you have to save the listbox items and their selected state in the workbook or some data support such as a file or database. When showing your form, you'll just read back the saved items and selected states.

Assuming you can save the list's content in the workbook, you can use something like the following:

Public Sub SaveList(ByVal plstListBox As MSForms.ListBox, ByVal prngSavePoint As Excel.Range)
    On Error GoTo errHandler

    Dim lRow As Long
    Dim bScreenUpdating As Boolean
    Dim bEnableEvents As Boolean

    bScreenUpdating = Application.ScreenUpdating
    bEnableEvents = Application.EnableEvents

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    prngSavePoint.CurrentRegion.Clear

    If plstListBox.ListCount > 1 Then
        For lRow = 0 To plstListBox.ListCount - 1
            prngSavePoint.Cells(lRow + 1, 1).Value = plstListBox.Selected(lRow)
            prngSavePoint.Cells(lRow + 1, 2).Value = plstListBox.List(lRow)
        Next
    End If

Cleanup:
    On Error Resume Next
    Application.EnableEvents = bEnableEvents
    Application.ScreenUpdating = bScreenUpdating
    Exit Sub

errHandler:
    MsgBox Err.Description, vbExclamation + vbOKOnly, "Error"
    Resume 'Cleanup
End Sub

Public Sub LoadList(ByVal plstListBox As MSForms.ListBox, ByVal prngSavePoint As Excel.Range)
    Dim lRow As Long
    Dim vntSavedList As Variant

    plstListBox.Clear

    If Not IsEmpty(prngSavePoint.Cells(1, 1).Value) Then
        vntSavedList = prngSavePoint.CurrentRegion.Value

        For lRow = 1 To UBound(vntSavedList, 1)
            plstListBox.AddItem vntSavedList(lRow, 2)
            plstListBox.Selected(lRow - 1) = vntSavedList(lRow, 1)
        Next
    End If
End Sub

To save (e.g. you could have a Save List button on your form), pass a reference to your listbox, and a reference to the top left cell of a free zone somewhere in your workbook. Beware that the code will write from this point down, on 2 columns, and overwrite everything that may be in its path. You must also be sure that this cell is isolated, i.e. not immediately adjacent to other content in any direction.

Example: SaveList ListBox1, Sheet1.Cells(1, 1)

You could have a Load List button on your form. To load back your list: LoadList ListBox1, Sheet1.Cells(1, 1)

The important listbox properties used in this answer are Selected and List, which give the selected state and label of any item in the list. These are zero-based indexed properties.