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.

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

"ZipCurs" wrote:

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.

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.

"Mike" wrote:

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

"ZipCurs" wrote:

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.

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



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

"Mike" wrote:

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

"ZipCurs" wrote:

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.

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


"Rick Rothstein (MVP - VB)" wrote in
message ...
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



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

"Mike" wrote:

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

"ZipCurs" wrote:

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.

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

"Rick Rothstein (MVP - VB)" wrote:

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


"Rick Rothstein (MVP - VB)" wrote in
message ...
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



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

"Mike" wrote:

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

"ZipCurs" wrote:

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.

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


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

"Rick Rothstein (MVP - VB)" wrote:

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


"Rick Rothstein (MVP - VB)" wrote
in
message ...
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



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

"Mike" wrote:

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

"ZipCurs" wrote:

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.

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

"Rick Rothstein (MVP - VB)" wrote:

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


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

"Rick Rothstein (MVP - VB)" wrote:

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


"Rick Rothstein (MVP - VB)" wrote
in
message ...
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



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

"Mike" wrote:

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

"ZipCurs" wrote:

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.

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

"ZipCurs" wrote:

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

"Rick Rothstein (MVP - VB)" wrote:

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


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

"Rick Rothstein (MVP - VB)" wrote:

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


"Rick Rothstein (MVP - VB)" wrote
in
message ...
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



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

"Mike" wrote:

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

"ZipCurs" wrote:

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.

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



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

"ZipCurs" wrote:

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

"Rick Rothstein (MVP - VB)" wrote:

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


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

"Rick Rothstein (MVP - VB)" wrote:

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


"Rick Rothstein (MVP - VB)"
wrote
in
message ...
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



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

"Mike" wrote:

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

"ZipCurs" wrote:

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.

Rick,

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

"Rick Rothstein (MVP - VB)" wrote:

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



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

"ZipCurs" wrote:

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

"Rick Rothstein (MVP - VB)" wrote:

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


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

"Rick Rothstein (MVP - VB)" wrote:

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


"Rick Rothstein (MVP - VB)"
wrote
in
message ...
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



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

"Mike" wrote:

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

"ZipCurs" wrote:

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