Selecting Multiple Items in a dropdown cell from a cascading data validation list

[tatooedsn]

Hi folks. I have a good one for you. I have a cascading data val list that I want to be able to select multiple items from. Say my first data val list has different animals, like dog, cat, bird, etc. And my next data val list that references the animals are animal colors. Say that I want to be able to select 3 or more of the colors in the same cell.. how would I do that? I found a VBA code on the web that allows multiple items to be selected but it only allows me to select 2 items.

My code for the 2nd validation list is "=INDIRECT(SUBSTITUTE(C3," ",""))".
The vba code I used:
Private Sub Worksheet_Change(ByVal Target As Range)
'Code by Sumit Bansal from Online Excel Tips & Tutorials
' 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 = "$C$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
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