ABC123U&ME
02-02-2017, 10:00 AM
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
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