Limit multiselect listbox

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
3,059
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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

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
3,059
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,273
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
3,059
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,273
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
3,059
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,273
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
 

Forum statistics

Threads
1,171,317
Messages
5,874,960
Members
433,083
Latest member
ychin130

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top