Forum Discussion
JoaoTeixeira
Apr 07, 2024MVP
Macro to select multiple values in Data Validation List
Hello everyone, I have this VBA code
Private Sub Worksheet_Change(ByVal Target As Range) Dim OldValue As String Dim NewValue As String Application.EnableEvents = True On Error GoTo Exitsub ...
JoaoTeixeira
MVP
Hi Hans, I have tried that... Is the logical solution, but unfortunately it doesn't work.
HansVogelaar
Apr 15, 2024MVP
Sorry about that. Here is a working version:
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("I:K,M:P")) Is Nothing Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo ExitSub
ElseIf 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
ExitSub:
Application.EnableEvents = True
End Sub