Preserving ListBox Selections
When using a MultiSelect ListBox, there’s no built-in way to preserve the user’s selections from one instance of a Userform to the next. When I need to do that, I use a hidden worksheet and store the selections in a range. Then I use the Initialize and QueryClose events to restore the selections.
Private Sub UserForm_Initialize()
Dim Rng As Range
Dim i As Long
’set up the listbox
Me.ListBox1.RowSource = “Sheet1!A1:A10″
Me.ListBox1.MultiSelect = fmMultiSelectMulti
‘define the range where the selections are stored
Set Rng = ThisWorkbook.Sheets(”lbSelections”).Range(”lbStored”)
‘Re-select the stored selections
For i = 0 To Me.ListBox1.ListCount - 1
Me.ListBox1.Selected(i) = Rng.Cells(i + 1)
Next i
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Dim i As Long
Dim Rng As Range
‘define the range where the selections are stored
Set Rng = ThisWorkbook.Sheets(”lbSelections”).Range(”lbStored”)
‘clear previously stored selections
Rng.Parent.UsedRange.ClearContents
’store the current selections
For i = 0 To Me.ListBox1.ListCount - 1
Rng.Cells(i + 1).Value = Me.ListBox1.Selected(i)
Next i
‘redefine the named range just in case the number of items have changed
Rng.Parent.Names.Add “lbStored”, _
Rng.Parent.Range(”A1″).Resize(Me.ListBox1.ListCount)
End Sub
The hidden worksheet will show a bunch of TRUE and FALSE entries that correspond to what was selected. These values are then used to set the Selected property.
I have constructed a MultiSelect ListBox (20 items) that is supposed to allow a user to select items. I am unable to record and use the selections in a range in the spreadsheet.
I tried to use the code above but the line:
For i = 0 To Me.ListBox1.ListCount - 1
results in a compile error.