multiple items from single drop down list on a sheet that has multiple drop down lists that I do not want multiple choices

[mdkusername]

I have an Excel (2016) workbook, on sheet 1 there are multiple drop down lists. On 3 of them I would like to choose multiple items. The VBA code I used changes all of the drop down lists to have multiple items but I would like to specify the drop down lists on the sheet that have multiple item choice. I added the code that allows multiple items on the all of the drop down lists on a sheet.
Thanks

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated: 2016/4/12
Dim xRng As Range
Dim xValue1 As String
Dim xValue2 As String
If Target.Count > 1 Then Exit Sub
On Error Resume Next
Set xRng = Cells.SpecialCells(xlCellTypeAllValidation)
If xRng Is Nothing Then Exit Sub
Application.EnableEvents = False
If Not Application.Intersect(Target, xRng) Is Nothing Then
xValue2 = Target.Value
Application.Undo
xValue1 = Target.Value
Target.Value = xValue2
If xValue1 <> "" Then
If xValue2 <> "" Then
If xValue1 = xValue2 Or _
InStr(1, xValue1, ", " & xValue2) Or _
InStr(1, xValue1, xValue2 & ",") Then
Target.Value = xValue1
Else
Target.Value = xValue1 & ", " & xValue2
End If
End If
End If
End If
Application.EnableEvents = True
End Sub

 

[jorismoerings]

Hi,

This caused by the usage of Cells.SpecialCells(xlCellTypeAllValidation) which basically will check if the cell you've changed contains data-validation disregarding the cell it's in.
You can prevent this by eliminating the usage of Cells.SpecialCells(xlCellTypeAllValidation) but instead check the validation.type of the target cell.

A detailed description on "how to ...." can be found on Contextures
Look at the part where the multiple list on a protected worksheet is described.