Chip
Tue Jul 22 16:04:59 CDT 2008
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)
"Pedro Devoto" <Pedro Devoto@discussions.microsoft.com> wrote in message
news:9A7A1814-9561-402D-B84A-535AF0C30C3D@microsoft.com...
>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