Limit No of Listbox Selections

[DubaiDave]

I would like to limit the number of selections a User may make in a Listbox to say 3. Is there a way? Could not find anything on Google or a search here.

Cheers

DubaiDave

 

[Mr. B]

Here is code that will limit the selections in a listbox to 3 (or another number you specify). I used this code in a list box named: lstItems

You will need to substitue the actual name of your list box.

Private Sub lstItems_AfterUpdate()
If Me.lstItems.ItemsSelected.Count > 3 Then
If Me.lstItems.ItemsSelected.Count <> 0 Then
MsgBox "Only three items may be selected!", vbCritical + vbOKOnly
strEachVal = ""
For Each varItem In Me.lstItems.ItemsSelected
strEachVal = Me.lstItems.ItemData(varItem)
If InStr(1, strSelectedValues, strEachVal) = 0 Then
'this is the value that was the last one selected so unselect it
Me.lstItems.Selected(varItem) = False
End If
Next varItem

End If

Else
'write all currently select values to the "strSelectedValues" variable
If Me.lstItems.ItemsSelected.Count <> 0 Then
strSelectedValues = ""
For Each varItem In Me.lstItems.ItemsSelected
If strSelectedValues = "" Then
strSelectedValues = Me.lstItems.ItemData(varItem)
Else
strSelectedValues = strSelectedValues & "," & Me.lstItems.ItemData(varItem)
End If
Next varItem
Else
MsgBox "No items are selected from the list", vbInformation
Exit Sub 'Nothing was selected
End If
End If
End Sub

HTH

 

[DubaiDave]

Mr B,

Just the ticket. Thank you very much.

DubaiDave

 

[Mr. B]

You are quite welcome. Glad to help.

 

[DubaiDave]

Mr B,

I am using this code.

Reading your comments, it would seem that the intent is to unselect the last item selected (i.e. the fourth) leaving you with the first three selected once again.

However, actual behaviour is to unselect all selected items once more than three items are selected.

Or have I misunderstood?

DubaiDave

 

[Mr. B]

From your original post I understood that you wanted to allow users to select only three options. The code that I posted will notify users when they have slected more than 3 options. It then clears their last selection.

Keep in mind that if the user wants to make a different selection for the 3 choices, then all they have to do is unselect any one of their previous 3 selections and then they can select any other options until they have selected a total of 3.

If I have misunderstood what you wanted, I am sorry.

 

[DubaiDave]

Mr B,

Thank you for your quick reply.

I must be doing something wrong then because if I select more than three, then all selections are being cleared, not just the last one (i.e. leaving me with no items selected).

I have cut and pasted your code and so it must be something to do in the way I have set up my listbox.

I just wanted to check that the expected behaviour is for the last selection to be cleared and not all. You have confimed this..so I will try to figure out where I am going wrong.

Thanks for your help once again.

DubaiDave

 

[Mr. B]

If I remember correctly I tested the code using the "Simple" setting for the Multi select property. I will look at my code again and see if I missed anything.

 

[DubaiDave]

Hello Mr B,

I changed the multiselect property from extended to simple, but the behaviour has not changed.

How does it behave in your application. Are all selections cleared or just the las one made?

DubaiDave

 

[DubaiDave]

Mr B,

I am a newbie VBA programmer, but it would seem that the routine relies upon strSelectedValues to contain a list of the selected items and if strEachVal is not in that string then it is deletd.

But this string is empty (it is set in the second half of the code) - surely the contents are wiped each time the sub is exited and it is not set again before the compare test. Thus the comapre fails and all items deselected. But I am not sure.

DubaiDave

 

[Mr. B]

Here is a demo database that works as I have tested the code. It only removes the last item selected.

You are correct in that the "strSelectedValues" variable should contain a comma seperated string of up to the three items selected. Then if the number of items selected is greater than 3, the code simply checks to see if the selected value is in the string. If it is not, then that is the one that is deleted.

Check out the attached file and see what may be wrong. Basically we have to find out why the string variable is empty.

 

[Mr. B]

DubaiDave,

What value do you have as the Bound column of your listbox? If the value you have bound to the list box is not a string, then that may be your problem. Can you send me a copy of your database so I can take a look?

 

[DubaiDave]

Mr B,

The problem was down to me. Once I saw your example, the answer was obvious...

I had not declared the variables at the top (I guess this must preserve them outside of the Sub).

Dim strSelectedValues As String
Dim strEachVal As String
Dim varItem, varLastItem

After having done this, the last selection is removed.

Thank you for posting that example.

Cheers

DubaiDave

 

[Mr. B]

DubaiDave,

Actually, looking back at it, I should have included the declaration of the variables in my original post. Sorry about that.

Just FYI, you should be using the Option Explicit statement just above the Dim statements. This statement will require that all variables be declared before they can be used. This is quite helpful as you can now imagine. To make this statement to be present everytime you use VBA in Access for evey module (even form modules) go to the VBA code window. From the Tools menu, select the Options options. In the Editor tab place a check mark next to the "Require Variable Declaration" option. Changing this setting will not place the Option Explicit statement in existing modules but it will in new ones.

You may want to take a look in the Access VBA Help file for "Understanding Scope and Visibility" just so you get the whole picture for using variables.

HTH

 

[DubaiDave]

Mr B,

Thanks for the tips. Explicit added and option turned on.

Have also read that help note which clears things up.

Cheers

DubaiDave

 

[Mr. B]

Glad to help. If I helped you out, if you don't mind, click on the Scales icon at the top right corner of my post and help me increase my reputation.

Thahks.