Protecting a Cell but allowing data validation drop down list to work

[chrisgarcia78]

Hi, I have a cell that's proteced because it has a formula that I want to prevent it from being deleted, the same cell has a drop down list which does not work because the cell is locked, I understand that the formula will be deleted anyway after I select an option from the drop down list but thats ok, I just want to prevent it from being deleted accidentally before selecting an option from the drop down list. How do I make this work?
Thank you in advance.

 

[Artik]

Let's assume that the cell in question is B2.
Requirements:
1. The cell is not locked.
2. When defining the validation list, the checkbox “Ignore blank” must NOT be checked.
3. After the above steps, the worksheet is protected.

Paste the following event procedure into the sheet module:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$B$2" Then
        If Not Target.Validation.Value Then
            Application.EnableEvents = False
            Application.Undo
            Application.EnableEvents = True
        End If
    End If

End Sub

If the user pastes a value other than the one allowed by the validation list into cell B2, the action will be undone without warning. If the user types a value outside the list, he will receive a validation message.

Artik