Looping Listbox in Userform

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ZipCurs - 12 Mar 2008 18:30 GMT

I am using the following code which activates when the userform is changed.

Private Sub ListBox2_Change()

Dim NumberOfCategories As Integer
Dim PickCount As Integer

NumberOfCategories = Sheets("Parameters").Range("C6") - 1

'Determine the number of items selected
PickCount = 0
For x = 1 To NumberOfCategories
With ListBox2
If .Selected(x) Then
PickCount = PickCount + 1
End If
End With
Next x

'If "ALL" selected turn everything else off ("ALL" is the first line)
If ListBox2.Selected(0) And PickCount > 0 Then
With ListBox2
For x = 1 To NumberOfCategories
.Selected(x) = 0
Next x
End With
End If

I have 2 problems. The code the count the number of items selected fails at
x=2. More importantly the "ALL" code causes the code to loop continuously.

Is there a way to set the change event to false at the bottom of the code to
prevent continuous looping? What other option do I have? Thank you in
advance.

Reply to this Message

Mike - 12 Mar 2008 20:01 GMT

This will do your count of whats in the listbox
Sub listbox()
For i = 0 To ListBox2.ListCount - 1
If ListBox2.Selected(i) Then
MsgBox i
End If
Next i
End Sub

> I am using the following code which activates when the userform is changed.
>
[quoted text clipped - 30 lines]
> prevent continuous looping? What other option do I have? Thank you in
> advance.

Reply to this Message

ZipCurs - 13 Mar 2008 00:31 GMT

Mike,

Thanks for your response. While this is not exactly what I am after, it is
a step in the right direction. I forgot to mention that this is a multipick
listbox, so I really need to know how many items have been selected. With
your input, I have figured that one out.

The real issue is getting the Event_Change feature to work only when manual
changes are made rather than automated ones. It seems like you should be
able to prevent this.

Again, any help would be appreciated.

> This will do your count of whats in the listbox
> Sub listbox()
[quoted text clipped - 39 lines]
> > prevent continuous looping? What other option do I have? Thank you in
> > advance.

Reply to this Message

Rick Rothstein (MVP - VB) - 13 Mar 2008 07:18 GMT

Does this code do what you want?

' These two lines go in the (General)(Declarations) section
Dim PickCount As Long
Dim Processing As Boolean

Private Sub ListBox2_Change()
Dim X As Long
If Processing Then Exit Sub
With ListBox2
PickCount = 0
Processing = True
If .ListIndex > 0 Then .Selected(0) = False
If .Selected(0) Then
For X = 1 To .ListCount - 1
.Selected(X) = False
Next
PickCount = -1
Else
For X = 1 To .ListCount - 1
If .Selected(X) Then PickCount = PickCount + 1
Next
End If
Processing = False
End With
Label1.Caption = PickCount
End Sub

Rick

> Mike,
>
[quoted text clipped - 59 lines]
>> > prevent continuous looping? What other option do I have? Thank you in
>> > advance.

Reply to this Message

Rick Rothstein (MVP - VB) - 13 Mar 2008 07:24 GMT

I forgot to mention... put a Label control on your form... the code updates
this Label (named Label1) with the value of PickCount (that is, it displays
the number of selected items). I wasn't sure what you wanted PickCount to be
when ALL was selected, so I simply set it to -1 so that it could easily be
tested for.

Rick

> Does this code do what you want?
>
[quoted text clipped - 91 lines]
>>> > in
>>> > advance.

Reply to this Message

ZipCurs - 19 Mar 2008 16:29 GMT

Hello Rick,

Thanks for your detailed response. Unfortunately, I have been unable to
test this in detail due to other distractions and the fact that I implemented
a kludge that basically gets what I need done.

The question I should have asked from the beginning is how to limit the
number of items selected on a multiple select listbox. Once four has been
reached, I want to disable add, while still allowing items to be removed.

Thanks

> I forgot to mention... put a Label control on your form... the code updates
> this Label (named Label1) with the value of PickCount (that is, it displays
[quoted text clipped - 99 lines]
> >>> > in
> >>> > advance.

Reply to this Message

Rick Rothstein (MVP - VB) - 19 Mar 2008 18:08 GMT

There is no guaranteed way to tell you how to limit the selections without
seeing the code for your kludge (especially with special handling required
for the "All" item at the beginning of the list). Rather than start a round
of guessing that may or may not work, I think it would be best if you post
the code you are now using. I will tell you that I can make the code I
posted previously limit the number of selections to 4 maximum and handle the
"All" item in the way I think you want; but since you didn't try my original
code out to let me know if it does or does not do what you want
functionality-wise, I have no idea if the modifications I have in mind will
do what you want either. If you choose to try my code out, and if it works
for you, then I'll be happy to modify it for your new condition. Otherwise,
we need to see your kludge code.

Rick

> Hello Rick,
>
[quoted text clipped - 121 lines]
>> >>> > in
>> >>> > advance.

Reply to this Message

ZipCurs - 19 Mar 2008 22:56 GMT

Rick,

Fair enough. I will check out your code when I get a chance. My "kludge"
is not really a solution, I just ignore the problem.

Thanks

> There is no guaranteed way to tell you how to limit the selections without
> seeing the code for your kludge (especially with special handling required
[quoted text clipped - 136 lines]
> >> >>> > in
> >> >>> > advance.

Reply to this Message

ZipCurs - 20 Mar 2008 14:38 GMT

Rick,

Thank you for your help so far.

I ended up using much of the code you wrote and it works great. I added the
feature to limit the code to 4 items. For the time being, I got rid of the
select "ALL" feature since this was kind of confusing. Some questions:

- For some reason, the first change I make to the listbox doesn't stick. I
doesn't matter if I am adding or subtracting an item, it simply goes through
the cycle once and then reverts back to its previous state. Note that I load
listbox from outside of UserForm.
- Related to the about problem, the warning I added comes up twice if I add
an item in excess of 4 of the first click.

' These three lines go in (General)(Declarations) section
Dim PickCount As Long
Dim Processing As Boolean
Dim OldList As String

Private Sub ListBox2_Change()
Dim X As Long
Dim NewList As String
Dim Msg, Style, Title, Help, Ctxt, Response, MyString

If Processing Then Exit Sub

NewList = ""
With ListBox2
PickCount = 0
Processing = True
For X = 0 To .ListCount - 1
If .Selected(X) Then
PickCount = PickCount + 1
NewList = NewList & "1"
Else
NewList = NewList & "0"
End If
Next

If PickCount < 5 Then
OldList = NewList
Else
For X = 0 To .ListCount - 1
If Mid(OldList, X + 1, 1) = "1" Then
.Selected(X) = True
Else
.Selected(X) = False
End If
Next X

'Check for excessive amounts of filter parameters
Msg = "A maximum of 4 individual filter items may be selected.
Please remove items before adding more or check the 'ALL' box below if you
would like to select all of the items." ' Define message.
Style = vbOKOnly ' Define buttons.
Title = "Filter Parameter Limit" ' Define title.
Response = MsgBox(Msg, Style, Title)
End If

Processing = False
End With
End Sub

> Rick,
>
[quoted text clipped - 143 lines]
> > >> >>> > in
> > >> >>> > advance.

Reply to this Message

Rick Rothstein (MVP - VB) - 20 Mar 2008 16:41 GMT

I changed the logic of your code somewhat in order to minimize the number of
variables needed (some of which were introduced to handle your "All" item
that has been removed). One of the variable I removed was NewList and,
because of this, the variable named OldList has a name that seems
inappropriate now (perhaps changing it to ListSelections would be more
descriptive). I added an explanation symbol to your MessageBox and changed
the wording of your alert message (mainly since it still referred to the
"All" item).

I am not sure if the changes I made in your code (see the new code below my
signature) solve the problems you described or not, so you will have to try
it out and see. If it does not solve your problems, can you describe those
problems in a little more detail? I wasn't sure what you meant about
adding/subtracting items (where and how are you doing this?) not "sticking".
Also, where are you loading the ListBox from if not from within the
UserForm? And I don't see the warning message come up more than once, but
you might have other code somewhere doing this; if so, can you post your
other code?

Rick

' These three lines go in (General)(Declarations) section
Dim PickCount As Long
Dim OldList As String

Private Sub ListBox2_Change()
Dim X As Long
Dim NewList As String
Dim Msg As String
With ListBox2
PickCount = 0
' Make OldList start out with as many
' zeroes as there are items in the list
OldList = String(.ListCount, "0")
For X = 0 To .ListCount - 1
If .Selected(X) Then
PickCount = PickCount + 1
'Check for excessive amounts of filter parameters
If PickCount > 4 Then
' Turn off the selection just made because
' it would have been the 5th selected item
.Selected(.ListIndex) = False
' Now warn the user he/she picked too many items
Msg = "A maximum of 4 individual filter items may " & _
"be selected at one time!" & vbCrLf & vbCrLf & _
"You may remove one or more items from your " & _
"current selection in order to select a " & _
"different set of items."
MsgBox Msg, vbOKOnly Or vbExclamation, _
"Filter Parameter Limit"
Else
' Stuff a "1" into the Xth position of OldList
Mid(OldList, X + 1) = Abs(.Selected(X))
End If
End If
Next
End With
End Sub

> Rick,
>
[quoted text clipped - 243 lines]
>> > >> >>> > in
>> > >> >>> > advance.

Reply to this Message

ZipCurs - 20 Mar 2008 17:58 GMT

Rick,

I am all set with this. Thanks for your help.

> I changed the logic of your code somewhat in order to minimize the number of
> variables needed (some of which were introduced to handle your "All" item
[quoted text clipped - 274 lines]
> >> > >> >>> > End If
> >> > >> >>> > End With

Reply to this Message