• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data Validation on multiple selection

Dear Expert,

Attached the excel document.

I have a blue drop down list that says - "Multiple List".

Is there a way when I select apple, orange and mango, the word multiple list will be remove it automatically?
Note: Cannot remove it from the data validation list.
 

Attachments

  • Multiple Selection.xlsm
    14.7 KB · Views: 12
Possibly...
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Oldvalue As String
    Dim Newvalue As String

    If Target.Address <> "$C$2" Then Exit Sub
    If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then Exit Sub
    If Target.Value = "" Then Exit Sub
    
    Application.EnableEvents = False
    On Error GoTo Exitsub
    If Target.Value = "Multiple List" Then Target.Value = vbNullString
    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
    Application.EnableEvents = True
    Exit Sub
Exitsub:
    Application.EnableEvents = True
End Sub
 
Back
Top