VBA Code

Adding Data Validation

Selection.Validation.Add(Type:=xlDVType.xlValidateList 
                         AlertStyle:=xlDVAlertStyle.xlValidAlertStop, _ 
                         Operator:=xlFormatConditionOperator.xlBetween, _ 
                         Formula1:="A,B,C,D", _ 
                         Formula2:= 

With Selection.Validation 
    .Delete 
    .IgnoreBlank = True 
    .InCellDropdown = True 
    .ShowInput = True 
End With 

This can even be abbreviated to the following:

Selection.Validation.Add Type:=xlValidateList, Formula1:="Excel,Word,PowerPoint" 

Responding to a Data Validation Drop Down

It is possible to run a macro when the value is changed from a Data Validation drop-down box.
You can use the Worksheet_Change() event to detect when the value in the drop-down box has changed.

Private Sub Worksheet_Change(ByVal Target As Range) 
Dim irowno As Integer 
   If Target.Column = 3 And _ 
      Target.Row = 8 Then 
      
      irowno = Application.WorksheetFunction.Match(Range("C8").Value, Range("C2:C6")) 
      Range("C14").Formula = "Better Solutions - " & Range("C8").Value & " = " & Range("D" & 1 + irowno).Value 
   End If 
End Sub 

Multi Select for Data Validation

Be able to select multiple items from a drop-down

Private Sub Worksheet_Change(ByVal Target As Range) 
    Const DELIM As String = ", "          ' Change to vbLf for line breaks  
    Dim rng As Range, cell As Range 
    Dim newVal As String, oldVal As String 
    Dim parts As Variant, i As Long 
    Dim tmp As String, found As Boolean, hasValidation As Boolean 

    On Error GoTo ExitHandler 

    ' Act on the current column
    Set rng = Intersect(Target, Me.Range(Me.Cells(2, Target.Column), Me.Cells(Me.Rows.Count, Target.Column))) 

    If rng Is Nothing Then Exit Sub 

    Application.EnableEvents = False 

    For Each cell In rng 
        If cell.CountLarge > 1 Then GoTo NextCell  ' safety for multi-cell pastes  

        ' Only process cells that have Data Validation = List
        On Error Resume Next 
        hasValidation = (cell.Validation.Type = xlValidateList) 
        On Error GoTo ExitHandler 
        If Not hasValidation Then GoTo NextCell 

        newVal = CStr(cell.Value) 

        ' Allow deletes (user pressed Delete or chose blank)
        If Len(newVal) = 0 Then GoTo NextCell 

        ' Grab the previous value before the pick
        Application.Undo 
        oldVal = CStr(cell.Value) 

        found = False 
        tmp = "" 

        If Len(oldVal) > 0 Then 
            parts = Split(oldVal, DELIM) 
            For i = LBound(parts) To UBound(parts) 
                If StrComp(Trim$(parts(i)), newVal, vbTextCompare) = 0 Then 
                    ' Toggle off: remove the item
                    parts(i) = vbNullString 
                    found = True 
                End If 
            Next i 

            ' Rebuild without empty entries
            For i = LBound(parts) To UBound(parts) 
                If Len(Trim$(parts(i))) > 0 Then 
                    tmp = tmp & IIf(Len(tmp) > 0, DELIM, vbNullString) & Trim$(parts(i)) 
                End If 
            Next i 
        End If 

        If found Then 
            ' Item existed -> removed
            cell.Value = tmp 
        Else 
            ' Item new -> append
            cell.Value = IIf(Len(oldVal) > 0, oldVal & DELIM, vbNullString) & newVal 
        End If 

NextCell: 
    Next cell 

ExitHandler: 
    Application.EnableEvents = True 
End Sub