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

wrote in message
ups.com...
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

wrote in message
ups.com...
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" wrote in message
...
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

wrote in message
ups.com...
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