Fast way to clear Listbox selection

I have a large Listbox in a Userform that has 5 columns and can have up
to 1200 row entries. I have run into a curious behavior.

I have buttons to Select all entries in the Listbox and to Clear all
selections in the Listbox. My code is virtually idential to do the
Select All and Clear All. Here's the curious part: when I have ~ 1000
rows in the Listbox, it takes ~ 3 seconds to do a Clear All, but only ~
1 second to do the Select All. With a small number of rows, it
impossible to see any difference, but with a large number of entries it
takes noticeably longer to do the clear I have written the Clear All
code as fast as I can think of (no calculations within the loop). I
also tried turning off screen updating and calculations, but it doesn't
seem to make any difference.

Does anyone know of a faster way to do the clear? Here is my code.

Private Sub btnClearAll_Click()
Dim I As Integer
Dim count As Integer

With NameForm.ListBox1
count = .ListCount - 1
For I = 0 To count
.Selected(I) = False
Next I
End With
End Sub

The only difference between the Clear All code and the Select All
codeis False/True in line .Selected(I) = False.
Any ideas? Why would be it faster to do the selection?

Any info would be appreciated,
Thanks,
Jim

Jim,

I tried turning off multiselect, setting the selection to none (-1) and
tunring multiselect back on. It seems to do what you want:

Private Sub btnClearAll_Click()
Me.ListBox1.MultiSelect = fmMultiSelectSingle
Me.ListBox1.ListIndex = -1
Me.ListBox1.MultiSelect = fmMultiSelectMulti
End Sub

hth,

Doug

<[email protected]> wrote in message
news:[email protected]...
>I have a large Listbox in a Userform that has 5 columns and can have up
> to 1200 row entries. I have run into a curious behavior.
>
> I have buttons to Select all entries in the Listbox and to Clear all
> selections in the Listbox. My code is virtually idential to do the
> Select All and Clear All. Here's the curious part: when I have ~ 1000
> rows in the Listbox, it takes ~ 3 seconds to do a Clear All, but only ~
> 1 second to do the Select All. With a small number of rows, it
> impossible to see any difference, but with a large number of entries it
> takes noticeably longer to do the clear I have written the Clear All
> code as fast as I can think of (no calculations within the loop). I
> also tried turning off screen updating and calculations, but it doesn't
> seem to make any difference.
>
> Does anyone know of a faster way to do the clear? Here is my code.
>
> Private Sub btnClearAll_Click()
> Dim I As Integer
> Dim count As Integer
>
> With NameForm.ListBox1
> count = .ListCount - 1
> For I = 0 To count
> .Selected(I) = False
> Next I
> End With
> End Sub
>
> The only difference between the Clear All code and the Select All
> codeis False/True in line .Selected(I) = False.
> Any ideas? Why would be it faster to do the selection?
>
> Any info would be appreciated,
> Thanks,
> Jim
>

Hi,
When swithing from multiselect to singleselect, the listbox is rebuilt
internaly, and the selection is cleared. I don't kow if it is faster with
large list but it is easy and convenient:

Private Sub CommandButton1_Click()
With ListBox1
.MultiSelect = fmMultiSelectSingle
.MultiSelect = fmMultiSelectMulti 'switch back to multi
End With
End Sub

I hope this helps
--
Regards,
SĂ©bastien
<http://www.ondemandanalysis.com>


"Doug Glancy" wrote:

> Jim,
>
> I tried turning off multiselect, setting the selection to none (-1) and
> tunring multiselect back on. It seems to do what you want:
>
> Private Sub btnClearAll_Click()
> Me.ListBox1.MultiSelect = fmMultiSelectSingle
> Me.ListBox1.ListIndex = -1
> Me.ListBox1.MultiSelect = fmMultiSelectMulti
> End Sub
>
> hth,
>
> Doug
>
> <[email protected]> wrote in message
> news:[email protected]...
> >I have a large Listbox in a Userform that has 5 columns and can have up
> > to 1200 row entries. I have run into a curious behavior.
> >
> > I have buttons to Select all entries in the Listbox and to Clear all
> > selections in the Listbox. My code is virtually idential to do the
> > Select All and Clear All. Here's the curious part: when I have ~ 1000
> > rows in the Listbox, it takes ~ 3 seconds to do a Clear All, but only ~
> > 1 second to do the Select All. With a small number of rows, it
> > impossible to see any difference, but with a large number of entries it
> > takes noticeably longer to do the clear I have written the Clear All
> > code as fast as I can think of (no calculations within the loop). I
> > also tried turning off screen updating and calculations, but it doesn't
> > seem to make any difference.
> >
> > Does anyone know of a faster way to do the clear? Here is my code.
> >
> > Private Sub btnClearAll_Click()
> > Dim I As Integer
> > Dim count As Integer
> >
> > With NameForm.ListBox1
> > count = .ListCount - 1
> > For I = 0 To count
> > .Selected(I) = False
> > Next I
> > End With
> > End Sub
> >
> > The only difference between the Clear All code and the Select All
> > codeis False/True in line .Selected(I) = False.
> > Any ideas? Why would be it faster to do the selection?
> >
> > Any info would be appreciated,
> > Thanks,
> > Jim
> >
>
>
>

Sébastien,

Interesting. I didn't think to try it like that.

Doug

"sebastienm" <[email protected]> wrote in message
news:[email protected]...
> Hi,
> When swithing from multiselect to singleselect, the listbox is rebuilt
> internaly, and the selection is cleared. I don't kow if it is faster with
> large list but it is easy and convenient:
>
> Private Sub CommandButton1_Click()
> With ListBox1
> .MultiSelect = fmMultiSelectSingle
> .MultiSelect = fmMultiSelectMulti 'switch back to multi
> End With
> End Sub
>
> I hope this helps
> --
> Regards,
> Sébastien
> <http://www.ondemandanalysis.com>
>
>
> "Doug Glancy" wrote:
>
>> Jim,
>>
>> I tried turning off multiselect, setting the selection to none (-1) and
>> tunring multiselect back on. It seems to do what you want:
>>
>> Private Sub btnClearAll_Click()
>> Me.ListBox1.MultiSelect = fmMultiSelectSingle
>> Me.ListBox1.ListIndex = -1
>> Me.ListBox1.MultiSelect = fmMultiSelectMulti
>> End Sub
>>
>> hth,
>>
>> Doug
>>
>> <[email protected]> wrote in message
>> news:[email protected]...
>> >I have a large Listbox in a Userform that has 5 columns and can have up
>> > to 1200 row entries. I have run into a curious behavior.
>> >
>> > I have buttons to Select all entries in the Listbox and to Clear all
>> > selections in the Listbox. My code is virtually idential to do the
>> > Select All and Clear All. Here's the curious part: when I have ~ 1000
>> > rows in the Listbox, it takes ~ 3 seconds to do a Clear All, but only ~
>> > 1 second to do the Select All. With a small number of rows, it
>> > impossible to see any difference, but with a large number of entries it
>> > takes noticeably longer to do the clear I have written the Clear All
>> > code as fast as I can think of (no calculations within the loop). I
>> > also tried turning off screen updating and calculations, but it doesn't
>> > seem to make any difference.
>> >
>> > Does anyone know of a faster way to do the clear? Here is my code.
>> >
>> > Private Sub btnClearAll_Click()
>> > Dim I As Integer
>> > Dim count As Integer
>> >
>> > With NameForm.ListBox1
>> > count = .ListCount - 1
>> > For I = 0 To count
>> > .Selected(I) = False
>> > Next I
>> > End With
>> > End Sub
>> >
>> > The only difference between the Clear All code and the Select All
>> > codeis False/True in line .Selected(I) = False.
>> > Any ideas? Why would be it faster to do the selection?
>> >
>> > Any info would be appreciated,
>> > Thanks,
>> > Jim
>> >
>>
>>
>>

Thanks Doug and Sebastien,
Now almost instantaneous even with large list.

Much appreciated!

Jim