Limit multiselect listbox

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,660
I would like to limit selections in a multiselect listbox to 2 selections. The third selection should be de-selected(?) following a messagebox indicating the limit. I'm close with the following code but I can't quite get the de-selection part. Any help will be appreciated. Dave
Code:
Private Sub ListBox2_Change()
Dim i As Integer, Cnt As Integer
Cnt = 0
For i = 0 To ListBox2.ListCount - 1
If ListBox2.Selected(i) = True Then
Cnt = Cnt + 1
End If
Next i
If Cnt > 2 Then
MsgBox "Only 2 types can be selected"
'de-select current selection here
End If
End Sub
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
I don't know if you can de-select. What you might do is setup a couple of variables you can use to set the first two selected values. Then if you get a hit on your routine above, (clear all selects) loop through and re-select the two you had previously.
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,660
Thanks Zack. I had tried something similiar but I kept getting a recursive error which required the task manager to fix. If de-selecting is not possible, I will continue my trials and post if I have any luck. Thanks. Dave
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,222
Maybe this is what you want?

Private Sub ListBox2_Change()
Dim iInd%
Dim i As Integer, Cnt As Integer
Cnt = 0
iInd = ListBox2.ListIndex
For i = 0 To ListBox2.ListCount - 1
If ListBox2.Selected(i) = True Then
Cnt = Cnt + 1
End If
Next i
If Cnt > 2 Then
ListBox2.Selected(iInd) = False
MsgBox "Only 2 types can be selected"
End If
End Sub
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,660

ADVERTISEMENT

That works perfect Tom. Thank you. Could you please explain how the "iInd% " works for listindex? Dave
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
Ah, nice Tom. ListIndex, of course. Should have thought of that. :)

Dave, you can use a one-liner w/o the addt'l variable..
Code:
        ListBox2.Selected(ListBox2.ListIndex) = False
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,222

ADVERTISEMENT

NdNoviceHlp said:
Could you please explain how the "iInd% " works for listindex? Dave
Each item in a ListBox can be referred to by its position, or index number, just as worksheets or workbooks or other such objects in a collection. And, Zack's suggestion of avoiding a variable is a better idea than I had, since my way was defining the iInd variable with each change, when really it only matters when the selection count is greater than 2 as his suggestion more efficiently showed.
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,660
Zack that was the missing syntax I needed. Thank you. Tom after a few unsuccessful attempts trialing the syntax for the list index property I assumed that the multi format was the problem. The MS help... "When the MultiSelect property is set to Extended or Simple, you must use the list box's Selected property to determine the selected items" .... seemed to mean that using listindex wasn't allowed. I was unsure of what the % did? I'll find out. Thanks again for the help. Dave
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,222
The % thing is an abbreviation in VBA for "as Integer".

Dim iInd%
is the same as
Dim iInd as Integer

Other abbreviations for variable types:

& Long
! Single
# Double
$ String
@ Currency
 

Watch MrExcel Video

Forum statistics

Threads
1,118,088
Messages
5,570,144
Members
412,306
Latest member
fabio6
Top