How to program the after no selection?

[Falinaicare]

Dear Experts

I have a question here. I have created a selection list that says no to the Fruit Items.
How to create that in VBA programming? Hope to hear from you soon.

Thanks.

 

[RDAngelo]

Sub test()
    With Cells(3, 5).Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:="=$A$2:$A$3"
    End With
    With Cells(6, 5).Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:="=$C$2:$C$6"
    End With
End Sub

 

[Falinaicare]

Dear RDAngelo

Thanks for your coding but where should I insert this coding?

Hope to hear from you soon.

 

[RDAngelo]

Use a standard code module

 

[Falinaicare]

Dear RDAngelo

Thanks for your Excel attachment.

The Purchase Fruit, if select "no" it should freeze the fruit items selection.

Hope that helps.

 

[RDAngelo]

Replace the code in the worksheet module with this...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Oldvalue As String, Newvalue As String

    Application.EnableEvents = True
    If Not Intersect(Target, Range("E6")) Is Nothing Then
        Application.EnableEvents = False
        If Range("E3").Value = "Yes" Then
            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
        Else
            Target.Value = vbNullString
        End If
    End If
    Application.EnableEvents = True
End Sub