Is there a way to limit the number of selections a user can choose on a ListBox with MultiSelect enabled in Access 2003? Right now I have a procedure that fires on the On Click event that checks the number of choices selected and if it is over my threshold it will display a warning label.
A:
Using the listbox ItemsSelected collection? That's the only way that I know of to limit the number of selected items. OTOH there are some truly twisted individuals out there who might have figured out an alternative so I never say never.
What's the problemw with this method?
Tony Toews
2009-09-10 19:05:27
I presume the OP would like to either prevent more than N selection from being selected or at least be able to deselect the most recent selection when they kick back their error/warning message.
CodeSlave
2009-09-10 19:19:27
+1
A:
Give this a try. It basically deselects the last item selected if its over the predefined limit:
Private Sub ListBox1_Change()
Dim counter As Integer
Dim selectedCount As Integer
selectedCount = 0
For counter = 1 To ListBox1.ListCount Step 1
If ListBox1.Selected(counter - 1) = True Then 'selected method has 0 base
selectedCount = selectedCount + 1
End If
Next counter
If selectedCount >= 4 Then 'modify # here
ListBox1.Selected(ListBox1.ListIndex) = False 'listindex returns the active row you just selected
MsgBox "Limited to 4 Choices", vbInformation + vbOKOnly, "Retry:"
End If
End Sub
Fink
2009-09-10 21:55:43
Thank you. ListBox.Selected(ListBox.ListIndex) was what I was looking for.
mandroid
2009-09-10 23:38:37
Actually no that doesn't deselect the last item selected. It will dselect the last item in the list though which may not be visible. Also you can get the exact list using the following rather than having to check each listbox item: Me.List11.ItemsSelected.Count
Tony Toews
2009-09-11 00:38:49
aha, i knew there was a quicker way to return the selected count! I don't follow how it returns the last item in the list... listIndex returns the active index number in the list, unless there is no index selected, then it returns the count of the list.http://msdn.microsoft.com/en-us/library/aa196331(office.11).aspx
Fink
2009-09-11 13:43:55
He is correct. ListBox.Selected(ListBox.ListIndex) = False deselects the last item selected. Just tested it out myself. You are correct about ItemsSelected.Count though :)
mandroid
2009-09-11 18:58:19
Ah, ok, now that I think about it I see where I'm wrong. The ListIndex is the key.
Tony Toews
2009-09-11 20:08:54
A:
I would suggest that a much more manageable, user-friendly and understandable UI for this would be the paired listbox approach, with ADD> and button after the user has reached the limit. You don't have to do anything difficult, just check the ListCount of the right-hand listbox and disable the ADD> button when it reaches the limit.
And you avoid many problems as it's quite clear to the user what they are doing in comparison to selecting multiple items at once in a single listbox. You could make the lefthand listbox multiselect and simply disable the ADD> button if the ItemsSelected count exceeds the limit, and notify the user appropriately.
David-W-Fenton
2009-09-12 02:27:11