Thursday, 29 August 2013

Limit number of selections in a multiselect ListBox in Excel

Limit number of selections in a multiselect ListBox in Excel

I have inserted the Form Control; ListBox with multiselect. But, I want to
limit the number of selections to 3.
If the user tries to select more than three it should deselect the one the
user tried to select. It should also appear a MsgBox notifying the user
that the maximum number of selections is 3.
I have tried to find the answer to this, and tried code snippets found
online but have not suceeded.
I have tried this code:
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
Source
But VBA returns "Run-time Error 424: object required" and highlights the
5. line.
I am a beginner in VBA, and do not have a lot of experience using it.
Why does the above code not work, and how can I solve my problem?

No comments:

Post a Comment