Select multiple values from the dropdown menu

[shafiey]

Multiple values can be selected from the drop-down menu with the following code.
I have three separate dropdown menus in three cells D1, F1 and H1 and I want this functionality only for the dropdown in cell F1.
Please guide me.
Thanks
VBA code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Const SEP As String = ", "
    Dim rngDV As Range
    Dim oldVal As String
    Dim newVal As String
    Dim arr, m, v
    If Target.Count > 1 Then GoTo exitHandler

    On Error Resume Next
    Set rngDV = Target.SpecialCells(xlCellTypeSameValidation)
    On Error GoTo exitHandler
    If rngDV Is Nothing Then Exit Sub
    
    newVal = Target.Value
    If Len(newVal) = 0 Then Exit Sub 'user has cleared the cell...
    
    Application.EnableEvents = False
    
    Application.Undo
    oldVal = Target.Value
    
    If oldVal <> "" Then
        arr = Split(oldVal, SEP)
        m = Application.Match(newVal, arr, 0)
        If IsError(m) Then
            newVal = oldVal & SEP & newVal
        Else
            arr(m - 1) = ""
            newVal = ""
            For Each v In arr
                If Len(v) > 0 Then newVal = newVal & IIf(Len(newVal) > 0, SEP, "") & v
            Next v
        End If
        Target.Value = newVal
    Else
        Target.Value = newVal 'EDIT
    End If

exitHandler:
      Application.EnableEvents = True
End Sub

 

[shafiey]

Multiple values can be selected from the drop-down menu with the following code.
I have three separate dropdown menus in three cells D1, F1 and H1 and I want this functionality only for the dropdown in cell F1.
Please guide me.
Thanks
VBA code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Const SEP As String = ", "
    Dim rngDV As Range
    Dim oldVal As String
    Dim newVal As String
    Dim arr, m, v
    If Target.Count > 1 Then GoTo exitHandler

    On Error Resume Next
    Set rngDV = Target.SpecialCells(xlCellTypeSameValidation)
    On Error GoTo exitHandler
    If rngDV Is Nothing Then Exit Sub
  
    newVal = Target.Value
    If Len(newVal) = 0 Then Exit Sub 'user has cleared the cell...
  
    Application.EnableEvents = False
  
    Application.Undo
    oldVal = Target.Value
  
    If oldVal <> "" Then
        arr = Split(oldVal, SEP)
        m = Application.Match(newVal, arr, 0)
        If IsError(m) Then
            newVal = oldVal & SEP & newVal
        Else
            arr(m - 1) = ""
            newVal = ""
            For Each v In arr
                If Len(v) > 0 Then newVal = newVal & IIf(Len(newVal) > 0, SEP, "") & v
            Next v
        End If
        Target.Value = newVal
    Else
        Target.Value = newVal 'EDIT
    End If

exitHandler:
      Application.EnableEvents = True
End Sub

I found another code and the answer was in the continuation. I leave the code for friends to use.

new VBA code:

Private Sub Worksheet_Change(ByVal Target As Range)
'Code by Sumit Bansal from https://trumpexcel.com
' To allow multiple selections in a Drop Down List in Excel (without repetition)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Address = "$D$1" Then
  If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
  Else: If Target.Value = "" Then GoTo Exitsub Else
    Application.EnableEvents = False
    Newvalue = Target.Value
    Application.Undo
    Oldvalue = Target.Value
      If Oldvalue = "" Then
        Target.Value = Newvalue
      Else
        If InStr(1, Oldvalue, Newvalue) = 0 Then
            Target.Value = Oldvalue & ", " & Newvalue
      Else:
        Target.Value = Oldvalue
      End If
    End If
  End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True

End Sub

Q: In the VBA code, the functionality is for cell C2 only. How do I get it for other cells?

Ans: To get this multiple selection drop-down in other cells, you need to modify the VBA code in the backend. Suppose you want to get this for C2, C3, and C4, you need to replace the following line in the code:

If Target.Address = "$C$2" Then

with this line:

If Target.Address = "$C$2" Or Target.Address = "$C$3" Or Target.Address = "$C$4" Then

 

[Akuini]

@shafiey
If you're willing to use an add-in, you can use a free Excel add-in called "Search deList" . You can find it here:
Search deList
Features:
Get unique, sorted & non-blank list.
Search by multiple keywords.
Several ways to search, like using AND or OR or LIKE operator , with or without keyword order.
Sort the list by original order or ascending order.
Widen or shorten the combobox width at run time.
Insert multiple entries into the cell.