Multiple selections in a Drop Down

[Brown]

Good Afternoon,
I am trying to create a drop down that will allow multiple selections. I have found that I need to use VBA. You can see what I have written, but I think this is for the list of itmes being on the same page. I have one sheet with the lists for the drop down and another sheet where I need the drop down. Can anyone assist me?

Thanks
Brown

 

[Sam_D_Ben]

Please try this, Paste this code in two different locations.

This code in sheet "Counselor Data Provider3"

Private Sub Worksheet_Change(ByVal T As Range)
    Dim O$, N$, D As Object, C As Object, V$, wsD As Worksheet, wsC As Worksheet, L&
    
    On Error GoTo X
    Set wsD = Sheets("Drop Down Sheet"): Set wsC = Sheets("Counselor Data Provider3")
    
    L = wsD.Cells(Rows.Count, 2).End(3).Row
    Set D = wsD.Range("A2:A" & L): Set C = wsD.Range("B2:B" & L)
    
    Call UV(wsC.[H2], C): Call UV(wsC.[I2], D)
    
    If T.Address = "$H$2" Then
        If T.Validation.Type = 0 Or T.Value = "" Then GoTo X
        Application.EnableEvents = False
        N = T.Value: Application.Undo: O = T.Value
        T.Value = IIf(O = "F", N, O & ", , " & N)
    End If
X:  Application.EnableEvents = True
End Sub

This code in worksheet "Module"

Sub UV(C As Range, R As Range)
    Dim D As Object, V$, X As Range: Set D = CreateObject("Scripting.Dictionary")
    For Each X In R: If Not D.exists(X.Value) And X.Value <> "" Then D.Add X.Value, ""
    Next: If D.Count > 0 Then V = Join(D.keys, ",")
    
    With C.Validation
        .Delete: .Add 3, 1, 1, V: .IgnoreBlank = True: .InCellDropdown = True
    End With
End Sub

 

[kvsrinivasamurthy]

Your code is for H2 cell only.
Multiple cell depends upon the list selected for validation. Whether the list is on same sheet or different sheet it makes no difference.

 

[Brown]

Thank you both. I am trying this now.
One quick question, can I do VBA in a CSV file? I am not seeing this any this.

 

[John_w]

As stated, it doesn't matter which sheet contains the data validation items, as long as the Data Validation List Source references those items.

Thank you both. I am trying this now.

Or try the code at Add Deselect VBA Coding for Multiselect Dropdown, which allows multiple selection items and removes an already selected item if you select it again. Put it in the sheet module of the sheet containing the Data Validation dropdown, in your case the sheet "Counselor Data Provider3". For your situation, change this line:

    If Not Intersect(Target, Range("I:I,J:J,K:K,L:L,W:W,X:X,AI:AI,AT:AT,AU:AU,AV:AV,AW:AW,BA:BA,BB:BB,BC:BC")) Is Nothing Then

to:

    If Not Intersect(Target, Range("H:H")) Is Nothing Then

and it will work for any Data Validation cell in column H.

One quick question, can I do VBA in a CSV file? I am not seeing this any this.

You can't. Workbooks with code must be saved as a .xlsm or .xlsb file.

 

[Brown]

Thank you for that information, and for clarifying the CSV issue.
Brown