Multiple Entry Dependent Drop Down Macro

[ABC123U&ME]

Hi All,

I am a little new at this so forgive me if I am asking a dumb question. I have the below macro that a friend helped me with in order to select multiple entries on a drop down data validation menu for columns 3-9. Example (Validation List contains values list of such as A,B,C,D, then the user can select multiple values and could have values A,B,C,D appear on the cell). The problem I am running into is that there are dependent data validation lists. When I select multiple values the dependent lists do not show any option in the drop down. I click on the drop down menu and nothing appears.

Example
1st List
States:
Texas
California

2nd List
Texas Cities:
Dallas
Houston

3rd List
California Cities:
Los Angeles
San Francisco

With the below macro I can select Texas and Dallas or Houston will show, and the same with California, but when I select Texas and California, the dependent lists do not show any values. I click on the drop down arrow on the dependent columns and nothing appears. The dependent columns are columns 5 and 7. I was wondering if anyone could help?


MACRO:
' To Select Multiple Items from a Drop Down List in Excel


Dim Oldvalue As String
Dim Newvalue As String
Dim replaceWord As String


On Error GoTo Exitsub
If (Target.Column > 3 Or Target.Column < 9) And (Target.Row >= 1 And Target.Row <= 100) Then
'If Target.Address = "$F$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
ElseIf InStr(1, Oldvalue, Newvalue, vbTextCompare) > 0 Then
replaceWord = Newvalue & ", "
Target.Value = Replace(Target.Value, replaceWord, "")
replaceWord = ", " & Newvalue
Target.Value = Replace(Target.Value, replaceWord, "")
Target.Value = Replace(Target.Value, Newvalue, "")
Else
Target.Value = Oldvalue & ", " & Newvalue
End If
End If
End If
Application.EnableEvents = True


Exitsub:
Application.EnableEvents = True


End Sub

[JBeaucaire]

A dependent drop down requires there to be some sort of exact match for the text in the first cell to match to range of cells header for the dependent drop down.

If your dependent list is titled Dallas, then Dallas,California is not a match.

The only solution that comes to mind is creating even more named range lists that combine in your individual lists into all the combinations with the appropriate matching headers for the combo choices. Long way around, but it has the benefit of being simple to do.