[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Worksheet_Change([color=darkblue]ByVal[/color] Target [color=darkblue]As[/color] Range)
[color=green]'Multiselection Data Validation dropdown list[/color]
[color=green]'Selecting a list item toggles it from the selected entries[/color]
[color=darkblue]Dim[/color] oldVal [color=darkblue]As[/color] [color=darkblue]String[/color]
[color=darkblue]Dim[/color] newVal [color=darkblue]As[/color] [color=darkblue]String[/color]
[color=darkblue]Const[/color] cDelimiter [color=darkblue]As[/color] [color=darkblue]String[/color] = ", "
[color=darkblue]If[/color] Target.Count > 1 [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
[color=darkblue]If[/color] Target.Value = "" [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
[color=darkblue]If[/color] Intersect(Me.Cells.SpecialCells(xlCellTypeAllValidation), Target) [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
[color=darkblue]If[/color] Target.Validation.Type <> xlValidateList [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
[color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]GoTo[/color] ReEnable
Application.EnableEvents = [color=darkblue]False[/color]
newVal = Target.Value
Application.Undo
oldVal = Target.Value
[color=darkblue]If[/color] oldVal = "" [color=darkblue]Then[/color]
[color=green]'First entry[/color]
Target.Value = newVal
[color=darkblue]Else[/color]
[color=darkblue]If[/color] IsError(Application.Match(newVal, Split(oldVal, cDelimiter), 0)) [color=darkblue]Then[/color]
[color=green]'add entry[/color]
Target.Value = oldVal & cDelimiter & newVal
[color=darkblue]Else[/color]
[color=green]'remove entry[/color]
newVal = Replace(cDelimiter & oldVal & cDelimiter, cDelimiter & newVal & cDelimiter, cDelimiter)
newVal = Mid(newVal, Len(cDelimiter) + 1)
[color=darkblue]If[/color] Len(newVal) [color=darkblue]Then[/color] newVal = Left(newVal, Len(newVal) - Len(cDelimiter))
Target.Value = newVal
[color=darkblue]End[/color] [color=darkblue]If[/color]
[color=darkblue]End[/color] [color=darkblue]If[/color]
ReEnable:
Application.EnableEvents = [color=darkblue]True[/color]
[color=darkblue]If[/color] Err.Number <> 0 [color=darkblue]Then[/color] MsgBox Err.Description, vbCritical, "Error " & Err.Number
[color=darkblue]End[/color] [color=darkblue]Sub[/color]