List Box form control, max 5 selections

[MikkelDuif]

Hello,

I have a question regarding the List Box in Excel.
I am about to build a dashboard, and what I want to have is a list box where the user can make multiple selection, but no more than 5.

So far, I haven't managed to do it with the list box form control. Is it even possible?
I imagine it can be done as an ActiveX with some coding, but I would rather avoid that.

Thanks a lot!

/Mikkel

 

[RedBeard]

This should be useful for you:

Limit number of selections in a multiselect ListBox in Excel - Super User

 

[MikkelDuif]

This should be useful for you:

Limit number of selections in a multiselect ListBox in Excel - Super User

Thanks you very much.
I have modified the code a bit, so it returns a number to the left of my list, so I can do a vlookup on another sheet to get them in order.

Private Sub ListBox1_Change()
Dim iInd%
Dim i As Integer, Cnt As Integer
Cnt = 0
iInd = ListBox1.ListIndex
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then
Cnt = Cnt + 1
For x = 1 To 5
Sheets("SheetName").Range("ColumnLetter" & i + 1).Value = Cnt
Next x
Else
Sheets("SheetName").Range("ColumnLetter" & i + 1).ClearContents
End If
Next i
If Cnt > 5 Then
ListBox1.Selected(iInd) = False
Sheets("SheetName").Range("ColumnLetter" & i).Clear
MsgBox "Only X can be selected"
End If
End Sub