Limit multiselect listbox

NdNoviceHlp

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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

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,049
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,049

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

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
3,049
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,120
Messages
5,857,494
Members
431,882
Latest member
saaaaaaaaaaaaaaaaaaaaaa

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