For the rare occasion when you need to select multiple items from a list in Excel, the below VBA code will do the trick, complete with a nice selector panel. See the screen capture below for it in action.
This first code snippet should be placed in the worksheet where the multiple selection is needed. Replace the range with the range of cells in which the selection takes place (ie. clicking on any cell Q12 to Q30 will trigger the selector panel).
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Count = 1 Then 'Range of cells to have the pop-up userform If Not Intersect(Range("Q12:Q30"), Target) Is Nothing Then UserForm1.Show End If End If End Sub
Next, create a new blank user form and add the below. Replace the range with the range of cells that contains the list of options that can be selected.
Private Sub UserForm_Initialize() With ListBox1 .MultiSelect = fmMultiSelectMulti .ListStyle = fmListStyleOption plist = Sheets("source ws").Range("A23:A100") //replace the range here 'Filter Blanks For i = LBound(plist) To UBound(plist) If plist(i, 1) <> "" Then .AddItem Trim(plist(i, 1)) Next i End With End Sub Private Sub UserForm_Activate() 'Match Listbox selections with ActiveCell selections Dim strCell As String, i As Long strCell = ActiveCell.Value & MySeperator With ListBox1 For i = 0 To .ListCount - 1 .Selected(i) = InStr(1, strCell, .List(i), 1) Next i End With UserForm1.Caption = "Select Projects" CommandButton1.Caption = "OK" End Sub Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) 'Hide userform if Close "X" clicked If CloseMode = 0 Then Cancel = True Me.Hide End If End Sub Private Sub CommandButton1_Click() Dim i As Long, strTemp As String With ListBox1 'Read selections For i = 0 To .ListCount - 1 If .Selected(i) Then strTemp = strTemp & .List(i) & "," .Selected(i) = False ' Clear selections End If Next i End With If Len(strTemp) Then strTemp = Left(strTemp, Len(strTemp) - Len(MySeperator)) ActiveCell.Value = strTemp Else ActiveCell.ClearContents ActiveCell.Value = "-- Select Project --" End If UserForm1.Hide End Sub
Unfortunately, there is no method to achieve this without the use of macros as of Excel 2016.