Why wouldn't listbox's values in Excel stay selected at restart?

[Pedro Devoto]

I have some Activex controls in an Excel worksheet including a listbox. The
thing is that when I select some values from it, and I close the document
they won't be there anymore (when I open it again). I'd need to know how to
make them stay selected, or how to fill some cells with the items that I
select from the box.

Thanks in advance,

Pedro

 

[Chip Pearson]

The selected state of a item in a list box isn't saved with the file. Upon
closing, the memory allocated to the list box is dumped and everything
defaults to unselected. You can store the selected items in a defined name
with code like the following. Run the code

Sub SaveSelectedIndexes()
Dim S As String
Dim N As Long
With Sheet1.ListBox1
For N = 0 To .ListCount - 1
If .Selected(N) = True Then
S = S & CStr(N) & " "
End If
Next N
End With
ThisWorkbook.Names.Add Name:="SelectedItems", _
RefersTo:=S, Visible:=False
End Sub

Sub RestoreSelectedIndexes()
Dim V As Variant
Dim S As String
Dim N As Long
On Error GoTo ExitSub:
S = Replace(Replace( _
ThisWorkbook.Names("SelectedItems").RefersTo, Chr(34),
vbNullString), _
"=", vbNullString)
V = Split(Trim(S), " ")

For N = LBound(V) To UBound(V)
Sheet1.ListBox1.Selected(V(N)) = True
Next N
ExitSub:
End Sub

Run the procedure SaveSelectedIndexes when you want to save the selected
status and run RestoreSelectedIndexes when you want to restore the saved
selected state. In both procs, change 'Sheet1.ListBox1' to the appropriate
sheet and control name.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)