Multiple Selections in Dropdown?

[cmarie85]

Excel newbie here. I've seen a few threads on this but can't quite get it to work on my end. I originally used the code here: How to Make Multiple Selections in an Excel Drop-Down list and tried it on a test column. The only thing i changed was A2 in line 6 to Q2 to match my column. It worked perfectly! But my workbook has a total of 14 worksheets, each sheet has the exact same content (each sheet represents a different city in my case). So then I went in and repeated the data-validation in all sheets and then opened up the VBE editor and went sheet by sheet, posting that code into the VBE editor. Saved, exited. But now when I try to make multiple selections on any of the sheets, it doesn't work. I can only assume I did it wrong as far as having it apply to multiple sheets. But I just can't seem to figure it out. Help?

TLDR; I need to be able to make multiple selections in a single drop down list... on every sheet in my workbook.

VBA code used:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Not Intersect(Target, Range("Q2")) Is Nothing 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 & vbNewLine & Newvalue
         Else:
            Target.Value = Oldvalue
         End If
      End If
   End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

 

[ranman256]

dropdowns are for single selection,
listboxes can do multiple

Use the right tool for the right job. don't use a screwdriver to hammer in nails.

 

[Micron]

Put a break point at the start of the code and step thru (F8), watch the execution and check variable values after a line is executed. Can also check what your target values are. For all you know, your list may be in the wrong cell. BTW, doesn't work doesn't help much.
Can't say I totally disagree with the listbox comment, but sometimes solving a code problem teaches you something.

 

[cmarie85]

Put a break point at the start of the code and step thru (F8), watch the execution and check variable values after a line is executed. Can also check what your target values are. For all you know, your list may be in the wrong cell. BTW, doesn't work doesn't help much.
Can't say I totally disagree with the listbox comment, but sometimes solving a code problem teaches you something.

My apologies. "Doesn't work" in my case means I can only choose one selection at a time in the dropdown menu. When I say I'm a newbie at excel, oh boy I mean I know next to nothing. I know even less about listboxes than I do dropdowns. Maybe someone can point me to the best option?

Here's the deal. I track incidents for a child welfare agency. Some incidents result in an Abuse/Neglect finding, some don't. So as you can see in my excel below, I need to be able to mark n/a or mark which type of Abuse/Neglect was investigated. In some cases, there's only one - which is perfect as I can simply select one option. But in SOME cases, there are more than one type of A/N investigated so I need to be able to mark multiple options. And this is a list I track throughout the year so I'm always adding incidents - so that one Abused/Neglect column needs to be able to continue to grow and always have that same multiple select option.

Hopefully I explained that well. ? I'm a total novice when it comes to this stuff but i'm just trying to make my spreadsheet as efficient as possible. So if anyone can help or has a better idea, I'd really appreciate it!

Investigations - 2022.xlsm
	A	B	C	D	E	F	G	H	I
1	Office	Date	Family	Type	Invest #	Original Allegation	Findings Letter entered -date	Abuse/Neglect Findings
2	SA	6/24/21	Doe, Family	SAMP	n/a	n/a	6/25/21	n/a
3	SA	7/27/21	Doe, Family	HM Samp	n/a	n/a	7/27/21	n/a
4	SA	Unk	Doe, Family	RCCL	2765956	Supervision	7/29/21	n/a
5	SA	Unk	Doe, Family	RCCL	2761134	Discipline	8/2/21	n/a
6	SA	Unk	Doe, Family	RCCL	2766099	Environment	8/2/21	n/a
7	SA	8/6/21	Doe, Family	SAMP	n/a	n/a	8/11/21	n/a
8	SA	8/12/21	Doe, Family	SAMP	n/a	n/a	8/12/21	n/a
9	SA	8/19/21	Doe, Family	SAMP	n/a	n/a	8/20/21	n/a
10	SA	8/28/21	Doe, Family	SAMP	n/a	n/a	8/30/21	n/a
11	SA	Unk	Doe, Family	RCCL	2774480	Supervision	9/10/21	NegSup No
12	SA	8/10/21	Doe, Family	RCCL	2773251	Supervision	9/24/21	n/a
13	SA	Unk	Doe, Family	RCCL	2789944	Supervision	10/18/21	n/a
14	SA	11/1/21	Doe, Family	HM Samp	n/a	n/a	11/1/21	n/a
15	SA	Unk	Doe, Family	RCCL	2802200	Supervision	11/5/21	NegSup No
16	SA	11/22/21	Doe, Family	HM Samp	n/a	n/a	11/22/21	n/a
17	SA	11/23/21	Doe, Family	MONI	n/a	n/a	11/23/21	n/a
18	SA	12/1/21	Doe, Family	HM Moni	n/a	n/a	12/1/21	n/a
19	SA	Unk	Doe, Family	RCCL	2821235	Supervision	12/2/21	n/a
20	SA	Unk	Doe, Family	RCCL	2814335	Discipline	12/6/21	SxAb No
21	SA	Unk	Doe, Family	RCCL	2821803	Supervision	1/18/22	PhAb No
22	SA	Unk	Doe, Family	RCCL	2829853	Supervision	1/24/22	PhAb No
23	SA	Unk	Doe, Family	RCCL	2833019	SIR	1/25/22	n/a
24	SA	Unk	Doe, Family	RCCL	2822326	Supervision	1/28/22	n/a
25	SA	Unk	Doe, Family	RCCL	2848391	SxAb	2/17/22	SxAb No
26	SA	Unk	Doe, Family	SAMP	n/a	n/a	2/18/22	n/a
27	SA	2/22/22	Doe, Family	SAMP	n/a	n/a	2/25/22	n/a
28	SA	Unk	Doe, Family	RCCL	2864618	SIR	3/8/22	n/a
29	SA	3/17/22	Doe, Family	HM Samp	n/a	n/a	3/17/22	n/a
30	SA	4/13/22	Doe, Family	HM Samp	n/a	n/a	4/13/22	NegSup No	*PhAb No
31	SA	4/13/21	Doe, Family	MONI	n/a	n/a	Need	n/a
32	SA	Unk	Doe, Family	RCCL	2764149	Supervision	Need	n/a
33	SA	Unk	Doe, Family	RCCL	2818776	Need	Need	n/a
34	SA	12/28/21	Doe, Family	SAMP	n/a	n/a	Need	NegSup No
35	SA	Unk	Doe, Family	RCCL	2832480	Supervision	Need	n/a
6 SA (2)

Cells with Data Validation
Cell	Allow	Criteria
H1:H35	List	n/a, PhAb Yes, PhAb No, SxAb Yes, SxAb No, EmAb Yes, EmAb No, NegSup Yes, NegSup No, MedNeg Yes, MedNeg No
D:D	List	RCCL,SAMP,MONI,HM Samp,HM Moni
E:E	Custom	=OR(COUNTIF($E$1:$E$9878,E1)=1,E1="n/a")
A1:A35	List	Ama,Bville,Bwd,Burl,CC,Dal,ELP,Hou,MC,RR,SA,Ty,Vic,WF

 

[Micron]

"Doesn't work" in my case means I can only choose one selection at a time in the dropdown menu.

That code strings together multiple single selections and separates them with linefeeds. It's not a multi-select combo. There is no such thing in Access (unless the combo is based on a multi value field, which I'd say most db developers avoid) so I doubt it can behave any different in Excel either. Thus I can't see how you can say it ever worked as a multi select combo.

What I also see as problematic in that code is that if you have previously chosen a value like 15 (or 150 for example), you will not be able to pick 1. That might not be a limitation for you. Maybe it's doing what you want but you can't see the added values because of your row heights for rows with multiple selections? A listbox won't fix that either.

 

[cmarie85]

After some searching on this forum, I found a different VBA code that works.

Private Sub Worksheet_Change(ByVal Target As Range)

'Code by Sumit Bansal from https://trumpexcel.com
' To make mutliple selections in a Drop Down List in Excel

Dim Oldvalue As String
Dim Newvalue As String

On Error GoTo Exitsub
If Target.Address = "$H$2" 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
            Target.Value = Oldvalue & ", " & Newvalue
        End If
    End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

The only 'problem' now is that it ONLY works with a single cell in the column. I'm assuming this is because of the "$H$2" part. So how can I edit this code so that it works for the rest of the column? Including mini sheet so you can see that in cell H2, I was able to make multiple selections. But I could only make one selection in, for example, cell H4.

Investigations - 2022.xlsm
	A	B	C	D	E	F	G	H
1	Office	Date	Family	Type	Invest #	Original Allegation	Findings Letter entered -date	Abuse/Neglect Findings
2	SA	6/24/21	Doe, Family	SAMP	n/a	n/a	6/25/21	PhAb Yes, SxAb No, EmAb No
3	SA	7/27/21	Doe, Family	HM Samp	n/a	n/a	7/27/21	n/a
4	SA	Unk	Doe, Family	RCCL	2765956	Supervision	7/29/21	SxAb No
5	SA	Unk	Doe, Family	RCCL	2761134	Discipline	8/2/21	n/a
6	SA	Unk	Doe, Family	RCCL	2766099	Environment	8/2/21	n/a
7	SA	8/6/21	Doe, Family	SAMP	n/a	n/a	8/11/21	n/a
8	SA	8/12/21	Doe, Family	SAMP	n/a	n/a	8/12/21	n/a
9	SA	8/19/21	Doe, Family	SAMP	n/a	n/a	8/20/21	n/a
6 SA (2)

Cells with Data Validation
Cell	Allow	Criteria
H1:H9	List	n/a, PhAb Yes, PhAb No, SxAb Yes, SxAb No, EmAb Yes, EmAb No, NegSup Yes, NegSup No, MedNeg Yes, MedNeg No
D:D	List	RCCL,SAMP,MONI,HM Samp,HM Moni
E:E	Custom	=OR(COUNTIF($E$1:$E$9878,E1)=1,E1="n/a")
A1:A9	List	Ama,Bville,Bwd,Burl,CC,Dal,ELP,Hou,MC,RR,SA,Ty,Vic,WF

 

[Micron]

Target.Value = Oldvalue & ", " & Newvalue

That's one idea I had in mind when I pointed out the issues. Other was to have the code autosize the row.
Try "H:H" rather than H2. You have applied data validation to the column/range?
e.g.
Select that range, ribbon data tab>Data Tools group>validation, choose the list range to add the list values>OK
otherwise you'll be able to type in anything.

 

[cmarie85]

Just in case anyone was watching this thread for an answer, I finally got it. I changed it from "Target.Address = "$H$2" to "Target.Column = 8"

This allows multiple selections in a drown down, in a whole column.

Private Sub Worksheet_Change(ByVal Target As Range)

'Code by Sumit Bansal from https://trumpexcel.com
' To make mutliple selections in a Drop Down List in Excel

Dim Oldvalue As String
Dim Newvalue As String

On Error GoTo Exitsub
If Target.Column = 8 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
            Target.Value = Oldvalue & ", " & Newvalue
        End If
    End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

Investigations - 2022.xlsm
	A	B	C	D	E	F	G	H
1	Office	Date	Family	Type	Invest #	Original Allegation	Findings Letter entered -date	Abuse/Neglect Findings
2	SA	6/24/21	Doe, Family	SAMP	n/a	n/a	6/25/21	PhAb Yes, SxAb No, EmAb No
3	SA	7/27/21	Doe, Family	HM Samp	n/a	n/a	7/27/21	SxAb No
4	SA	Unk	Doe, Family	RCCL	2765956	Supervision	7/29/21	SxAb No
5	SA	Unk	Doe, Family	RCCL	2761134	Discipline	8/2/21	n/a, PhAb Yes, PhAb No
6	SA	Unk	Doe, Family	RCCL	2766099	Environment	8/2/21	n/a
7	SA	8/6/21	Doe, Family	SAMP	n/a	n/a	8/11/21	n/a
8	SA	8/12/21	Doe, Family	SAMP	n/a	n/a	8/12/21	n/a
9	SA	8/19/21	Doe, Family	SAMP	n/a	n/a	8/20/21	n/a
10	SA	8/28/21	Doe, Family	SAMP	n/a	n/a	8/30/21	n/a
11	SA	Unk	Doe, Family	RCCL	2774480	Supervision	9/10/21	EmAb Yes, NegSup Yes
6 SA (2)

Cells with Data Validation
Cell	Allow	Criteria
H1:H11	List	n/a, PhAb Yes, PhAb No, SxAb Yes, SxAb No, EmAb Yes, EmAb No, NegSup Yes, NegSup No, MedNeg Yes, MedNeg No
D:D	List	RCCL,SAMP,MONI,HM Samp,HM Moni
E:E	Custom	=OR(COUNTIF($E$1:$E$9878,E1)=1,E1="n/a")
A1:A11	List	Ama,Bville,Bwd,Burl,CC,Dal,ELP,Hou,MC,RR,SA,Ty,Vic,WF

 

[Micron]

You had the solution because that's what I suggested but using letters?

Try "H:H"